How can I split a collumn of numbers into sets of 3

Occasional Visitor

Hi there,

 

I have a collumn of data (162R x 1C) that I would like to split into 54R x 3C.

 

I have this

x

x

x

y

y

y

z

z

z

 

I want to get to this (where each number is in a different row)

x x x

y y y

z z z 

 

How can I do this automatically? I need to enter the data into another program where I need the data like this since each row will represent a triplicate of values.

 

Thank you for your help!

5 Replies

@dipshay17 

 

VBA code and it leaves original data intact...

 

Nothing_Left_to_Lose_0-1665966377879.png

 

VBA code...

'---

Sub StartMeUp()
Call BunchAndMove(3)
End Sub

 

'Transposes every lngStep rows into single adjacent row
Function BunchAndMove(lngStep As Long) As Byte
'Nothing Left to Lose -April 2009, August 2013, October 2022
Dim rngToMove As Excel.Range
Dim N As Long
Dim M As Long
Dim rCol As Long

Application.ScreenUpdating = False
Set rngToMove = Selection.Columns(1).Cells
rCol = 1
For N = 1 To rngToMove.Count Step lngStep
M = M + 1
With rngToMove.Parent
.Range(rngToMove.Cells(M, rCol + 1), rngToMove.Cells(M, rCol + lngStep)).Value = _
Application.WorksheetFunction.Transpose(rngToMove(N).Resize(lngStep, 1).Value)
End With
Next 'N
Application.ScreenUpdating = True
Set rngToMove = Nothing
End Function

'---

 

 

Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

 

@dipshay17 You need WRAPROWS() function.

=WRAPROWS(A1:A9,3)

Harun24HR_0-1665982329823.png

 

@dipshay17 

An alternative could be Power Query.

split columns in rows.JPG

@dipshay17 Based on the file provided by @Quadruple_Pawn , you could use the following query as well:

let
    Source = Table.Split (Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], 3),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.Transpose([Column1])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
    #"Expanded Custom"

 

Hi @dipshay17 

 

Another Power Query option based on @Quadruple_Pawn sample:

let
    Source = Table.Split(Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], 3),
    Transposed = List.Transform(Source, Table.Transpose),
    Combined = Table.Combine(Transposed)
in
    Combined