Recreating a column but listing each item multiple times

Try

=OFFSET($A$1,INT((ROW()-1)/3),0)

VBA Noob

Originally Posted by katie1343

I hope this is the right place to post this. Sorry if it is not. I could not find any posts that discuss what I am looking for. If someone knows of some and can point me in the right direction I would appreciate it.

I need excel to recreate a list I already have in a new column, but list each item on the list multiple times. It is a bit hard to explain, so for example my list looks like this:

(Column A)
Red
Green
Blue
Orange

And I need a new list to be created in a new column that looks like:

(Column B)
Red
Red
Red
Green
Green
Green
Blue
Blue
Blue
Orange
Orange
Orange

It seems like this should be possible, but I cannot make it work. In the new column I typed in (for example) =A1 three times, then =A2 three times and then I used auto fill. But the auto fill uses the adjacent cell rather than a cell above multiple times.

Hi,
try

=INDIRECT("A"&MOD(INT((ROW()-1)/3),4)+1)

hth
---

aaha - assuming that you wanted the set repeating, otherwise VBANoob's solution is shorter.
---