Forum Discussion
jeffheenan
Oct 07, 2020Copper Contributor
Copying cells from one column to multiple rows in a different column
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.
4 Replies
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)
- jeffheenanCopper Contributor
- Subodh_Tiwari_sktneerSilver Contributor
You're welcome! Glad we could help.
- Subodh_Tiwari_sktneerSilver Contributor
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.