Forum Discussion
Solved
One column into multiple columns
I have one long column (2000+ rows) of numerical data which needs to be converted to multiple columns of 50 rows each.
i.e
| 32 |
| 24 |
| 53 |
| 352 |
| 42 |
| 5 |
| 26 |
| 34 |
| 43 |
to this
| 32 | 352 | 26 |
| 24 | 42 | 34 |
| 53 | 5 | 43 |
Is there a quick way to do this?
Presuming numbers in A1:A9
1st option:
=WRAPCOLS(A1:A9,3)If WRAPCOLS is not available:
=INDEX(A1:A9,TRANSPOSE(SEQUENCE(3,3,1,1)))
Sub columns_of_50() Dim i As Long Dim j As Long Dim k As Long Dim m As Long Dim n As Long i = Range("A" & Rows.Count).End(xlUp).Row k = 1 m = 1 For j = 1 To i Cells(m + 3, k + 3).Value = Cells(j, 1).Value m = m + 1 If m = 51 Then k = k + 1 m = 1 Else End If Next j End SubAn alternative could be these lines of code. In the attached file you can click the button in cell C2 to run the macro.
Presuming numbers in A1:A9
1st option:
=WRAPCOLS(A1:A9,3)If WRAPCOLS is not available:
=INDEX(A1:A9,TRANSPOSE(SEQUENCE(3,3,1,1)))
