Dec 02 2022 09:19 AM
I have a single column of 70K plus entries that I want to split into multiple columns of about 50 each. Any suggestions?
Dec 02 2022 09:40 AM
Sub fifty()
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
i = Range("A" & Rows.Count).End(xlUp).Row
k = 3
m = 1
For j = 1 To i
Cells(m, k).Value = Cells(j, 1).Value
m = m + 1
If j Mod 50 = 0 Then
k = k + 1
m = 1
Else
End If
Next j
End Sub
Maybe with these lines of code. In the attached file you can click the button in cell B2 to run the macro.
Dec 02 2022 10:08 AM - edited Dec 02 2022 10:08 AM
Three possibilities with formulas:
=TRANSPOSE(INDEX(A2:A70001,SEQUENCE(ROUNDUP(70000/50,0),50)))
=WRAPCOLS(A2:A70001,50)
=MAKEARRAY(50,ROUNDUP(70000/50,0),LAMBDA(a,b,INDEX(A2:A70001,a+((b-1)*50))))
WRAPCOLS() is only available for Excel Insiders.
Dec 02 2022 10:47 AM
An alternative could be Power Query. In the attached file you can add data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse. Then you can select refresh to update the result.
Dec 04 2022 08:47 AM
As variant
hWrap = LAMBDA(array, n,
LET(
nR, ROWS(array),
IF(
nR <= n,
array,
LET(a, hWrap(DROP(array, n), n), IFNA(HSTACK(DROP(array, n - nR), a), ""))
)
)
)