Forum Discussion

dipshay17's avatar
dipshay17
Copper Contributor
Oct 16, 2022

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.

 

Thank you for your help!

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 17, 2022

    Hi dipshay17 

     

    Another Power Query option based on OliverScheurich sample:

    let
    Source = Table.Split(Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], 3),
    Transposed = List.Transform(Source, Table.Transpose),
    Combined = Table.Combine(Transposed)
in
    Combined
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 17, 2022

    dipshay17 Based on the file provided by OliverScheurich , 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"

     

  • Nothing_Left_to_Lose's avatar
    Nothing_Left_to_Lose
    Brass Contributor
    Oct 17, 2022

    dipshay17 

     

    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)

     

Resources