Copying cells from one column to multiple rows in a different column

[jeffheenan]

I have a large data set where I need to take several rows from one column and copy each row n amount of times to a different column. To be specific, lets say in column A, I have:

1

2

3

4

In column B, I would need:

1

1

1

1

2

2

2

2

3

3

3

3

etc.

As my actual "column A" data set has thousands of values, I was hoping to find some way to automate this process with a formula or macro.

[#2]

@jeffheenan 

If your data is as per the screenshot below, you may try something like this...

In B1

=INDEX(A:A,QUOTIENT(ROWS(A$1:A1)-1,4)+1)

and then copy it down.

[#3]

@jeffheenan 

In B1:

=INDEX(A:A,(ROW()-1)/4+1)

Fill down.

Alternatively, enter the number of repetitions in a cell, for example C1, and use

=INDEX(A:A,(ROW()-1)/$C$1+1)

[#4]

@Hans Vogelaar 

@Subodh_Tiwari_sktneer

Thanks guys. That is exactly what I was looking for.

[#5]

@jeffheenan 

You're welcome! Glad we could help.