turning 1,2,3 into 1,1,1,2,2,2,3,3,3

[slxia1]

in column A, i have 1,2,3
how to write a formular that turns into 1,1,1,2,2,2,3,3,3?
or 1,1,1,1,2,2,2,2,3,3,3,3?

[benishiryo]

hi slxia1, is it a maximum of just 3 numbers in column A? otherwise, it's just too long a formula & you might want to consider VBA. here's the current formula to handle 3 numbers:

Please Login or Register  to view this content.

change all those in red to 4 if you want it to repeat 4 times

[zbor]

And if your data is from A1 to A10 write this in B1 and pull down:

Please Login or Register  to view this content.

This will return you:
a,a,a,b,b,b,c,c,c,m,m,m,a,a,a,b,b,b, etc (in Column B).

To change from a,a,a, to a,a,a,a, change number 3 to 4 (or any other number for repetitions)

[slxia1]

Hi Guys,

I was hoping to avoid vba. Do you know a quick loop for it?

Also, thanks for the formula, but I have a lot of data. Ill try zbor's formula nevertheless.

[Marcol]

If you have more than 3 comma seperated values, to keep the formula manageable, you could consider using a named range,and helper columns (hidden with the +/- grouping button).

Name:= "Repeated"

Please Login or Register  to view this content.

This handles up to 6 values, extend the array if you need more.

Then drag B2 Across and Down until blanks are returned.

Lastly extend the concatenation in J2.

In B2

Please Login or Register  to view this content.

Change the value in Cell $J$1 to change the number of repeats per value.