Ask Your Question

How to transpose one column into multiple row

asked 2019-05-09 21:26:01 +0200

Liszur gravatar image

updated 2020-08-05 00:06:10 +0200

Alex Kemp gravatar image

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?




1 2 3
1 2 3

Thanks for your help!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-05-09 22:44:53 +0200

updated 2019-05-09 22:46:04 +0200

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.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

answered 2019-05-09 23:31:24 +0200

Lupp gravatar image

updated 2019-05-09 23:47:54 +0200

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
 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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-09 21:26:01 +0200

Seen: 397 times

Last updated: May 09 '19