# How to transpose one column into multiple row

Hey, I have a lot of numbers (25344) in one column and i want to transpose it into rows, where i want to start new row after every 8 number. How can i do this?

Like:

A
1
2
3
1
2
3


into:

A B C
1 2 3
1 2 3


edit retag close merge delete

Sort by » oldest newest most voted

Taking into account the data reported and considering in Sheet1 (Plan1), in Sheet 2 at A1 paste the formula =INDIRECT("Plan1.A"&(COLUMN()+((ROW()-1)*8))) drag to H1, and then drag down to line 3168. Then Copy and Paste value.

## Test file

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

more

Just for completeness:
A few years ago I sketched an array function for a purpose as presented here.
Applying user functions always comes with disadvantages, and array functions have specific disadvantages due to their need to lock an output range.

Anyway you may decide yourself if you want to try such a solution based on user code.

The code:

REM Now a primitive function, a sketch.
Function reArrangeRange(pSY As Long, pSX As Long, pSZ As Long, _
pSRows As Long, pSCols As Long, _
pTRows As Long, pTCols As Long)
REM pSY, pSX, pSZ give the source's first cell by row, column, sheet as 1-based indices.
REM pSRows and pSCols give the numbers of rows and of columns for the source range.
REM pTRows OR pTCols give the number of rows OR columns for the target range.
REM The other value must be 0.
The function produces and locks the needed outputrange and fills it with the rearranged data.
Dim numCells As Long, xS As Long, yS As Long, xT As Long, yT As Long, n As Long
numCells = pSCols * pSRows
If numCells=0 Then Exit Function
If pTCols<>0 Then
pTRows = Int(numCells/pTCols)
If pTRows * pTCols < numCells Then pTRows = pTRows + 1
Else
pTCols = Int(numCells/pTRows)
If pTRows * pTCols < numCells Then pTCols = pTCols + 1
End If
Dim doc0 As Object, sourceSheet As Object
Dim r(1 TO pTRows, 1 To pTCols) As Variant
doc0 = ThisComponent
sourceSheet = doc0.Sheets(pSZ-1)
sourceRange = sourceSheet.GetCellRangeByPosition(pSX-1, pSY-1, pSX-1 + pSCols-1, pSY-1 + pSRows-1)
sourceDA = sourceRange.DataArray
For xS = 1 To pSCols
For yS = 1 To pSRows
n  = (xS-1)*pSRows + yS
xT = Int((n-1) / pTRows) + 1
yT = (n-1) Mod pTRows + 1
r(yT, xT) = sourceDA(yS-1)(xS-1)
Next yS
Next xS
reArrangeRange = r
End Function


Assuming the data are in A2:A25345 of the first sheet, the formula for the given case should be:
{=REARRANGERANGE(2; 1; 1; 25344; 1; 0; 8)}
{=TRANSPOSE(REARRANGERANGE(2; 1; 1; 25344; 1; 8; 0))} entered for array-evaluation.

more