Split values from a single column to different individual columns in MS Excel

Question

Need to split the values available in parent column to three individual columns as shown in the picture.

Answer 1

A succinct alternative using MAKEARRAY:

=LET(n,3,A,A1:A9,m,ROWS(A),MAKEARRAY(m,n,
 LAMBDA(r,c,IF(c=MOD(r-1,n)+1,INDEX(A,r),""))))

or even a shorter, not using MAKEARRAY which is probably more efficient, since it doesn't use a LAMBDA function:

=LET(n,3,A,A1:A9,m,ROWS(A),IF(N(MOD(SEQUENCE(m,,0),n)+1=SEQUENCE(,n))=1,A,""))

where MOD(SEQUENCE(m,,0),n)+1 generates the sequence: {1;2;3;1;2;3...;1;2;3} until m for n=3 for example. It is actually the array version of MOD(r-1,n)+1 from the first formula.

and

N(MOD(SEQUENCE(m,,0),n)+1=SEQUENCE(,n))

stacks vertically n-times the unit matrix (i.e. MUNIT(n)), similar to what does the REDUCE/VSTACK approach, see below, but without using a LAMBDA function:

1   0   0
0   1   0
0   0   1
1   0   0
0   1   0
0   0   1
1   0   0
0   1   0
0   0   1

Here is the output (for the first approach):

Another alternative using REDUCE/VSTACK pattern(1), maybe less efficient for large dataset (when n is a small number, i.e. it requires a high number of iterations), but probably easier to understand:

=LET(n,3,A,A1:A9,u,MUNIT(n), x,REDUCE(u,SEQUENCE(n-1),
 LAMBDA(ac,_,VSTACK(ac,u))),IF(x=1,A,""))

(1) Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length.

Answer 2

Assuming entries in A1:A9:

=LET(
    λ,3,
    ζ,A1:A9,
    IF(
        MAKEARRAY(ROWS(ζ),λ,LAMBDA(α,β,INDEX(MUNIT(λ),1+MOD(α,λ),1+MOD(β,λ)))),
        IF(SEQUENCE(,λ),ζ),
        ""
    )
)

Amend the initial definition for λ (3 here) to determine the number of columns to return in the output.

Answer 3

with list starting at A3, modify accordingly

Sub CutAndMove()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 4 To lastRow Step 3
        Range("A" & i).Cut Destination:=Range("B" & i)
    Next i
    
    For i = 5 To lastRow Step 3
        Range("A" & i).Cut Destination:=Range("C" & i)
    Next i
End Sub