1

I am trying to dynamically expand a list to include duplicates. I managed to achieve this without VBA, but after condensing several columns containing intermediary functions I have tied myself in a knot of circular references. I'm not convinced I can get it working again, so I turn to SU for help.

I have a table as below:

        APPLE  BANANA   CHERRY  DATES
JOHN      x                x
PAUL              x
GEORGE    x       x
RINGO     x       x        x      x

I need to create from this two columns containing the following:

JOHN         APPLE
JOHN         CHERRY
PAUL         BANANA
GEORGE       APPLE
GEORGE       BANANA
RINGO        APPLE
RINGO        BANANA
RINGO        CHERRY
RINGO        DATES

That is, each individual instance of Fruit is listed. I am able to create the second column, i.e. the list of fruits according to the Eater; I just can't get the first column.

From here the list will be expanded again. Let's say I have another table:

FRUIT    LINES
APPLE      2
BANANA     3
CHERRY     1
DATES      2

The ultimate list I would like to end up with is:

JOHN         APPLE          1
JOHN         APPLE          2
JOHN         CHERRY         1
PAUL         BANANA         1
PAUL         BANANA         2
PAUL         BANANA         3
GEORGE       APPLE          1
GEORGE       APPLE          2
GEORGE       BANANA         1
GEORGE       BANANA         2
GEORGE       BANANA         3
RINGO        APPLE          1
RINGO        APPLE          2
RINGO        BANANA         1
RINGO        BANANA         2
RINGO        BANANA         3
RINGO        CHERRY         1
RINGO        DATES          1
RINGO        DATES          2

That is, each line is repeated according to the number in the "Fruit/Lines" table. I'm sure this second step is just a replication of the first step, rather than one complete set of functions.

Any help will be appreciated.

The duplicate question, Turn one row into multiple rows in Excel, doesn't cover the dynamic element; steps 5 and 6 are "copy list and paste values" and "filter column and delete nulls" are not suitable for this application.

Browse other questions tagged or ask your own question.