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.
