Need Help expanding a list....

**Sandman4432** · 08-01-2007, 12:09 PM

I have a long list of words:
e.g.:

apple
pear
banana
peach

and I want to expand the list to look like this for instance:
apple
apple
apple
apple
apple
pear
pear
pear
pear
pear
banana
banana
banana
banana
banana
peach
peach
peach
peach
peach

Now this may be easy for such a small number of words but my list is over 10,000 long and i need to expand each word about 50 times.... the other problem is that i need to keep them in the order they are in now...i cant use alphabetical sorting (which is where my problem comes in). What is a good formula to use to do this? Thanks

**Paul** · 08-01-2007, 12:32 PM

If your list is in A1:A10000, then in B1 put the formula:

=INDIRECT("A"&CEILING(ROW()/5,1))

Fill this down the entire column of B (since 10000 items in column A would translate to 50000 items in column B if you want 5 of each).

This should do what you're asking.. I hope.

**FrankBoston** · 08-01-2007, 12:38 PM

Instead of a macro, here is a quick filled down formula
Assuming your words are in A1:A10000

set B1: =OFFSET($A$1,(ROWS($A$1:A1)-1)/5,0)

Then do a long fill-down (to row 50,000+)

This gives you 5 result words for each col A word and preserves their order

I have also attached an example sheet