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

Occasional Visitor

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

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.

5 Replies

# Re: How can I split a collumn of numbers into sets of 3

VBA code and it leaves original data intact...

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)

# Re: How can I split a collumn of numbers into sets of 3

@dipshay17 You need WRAPROWS() function.

``=WRAPROWS(A1:A9,3)``

# Re: How can I split a collumn of numbers into sets of 3

An alternative could be Power Query.

# Re: How can I split a collumn of numbers into sets of 3

@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),
#"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"``````

# Re: How can I split a collumn of numbers into sets of 3

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