Looking to Split the Data into Row by Delimiter

Question

I have this large data in excel sheet just shared a little picture to achive the result. I have tried to find the fomrula online but found a code which solved the one problem but not other.

VBA code just splitting the column2 values into rows but how to sequence the Column1.

I would appreciate if both can be create using fomrula or code.

I would appreciate your help in this regards.

Data:

Result:

Sub SplitAll()
        Dim xRg As Range
        Dim xRg1 As Range
        Dim xCell As Range
        Dim I As Long
        Dim xAddress As String
        Dim xUpdate As Boolean
        Dim xRet As Variant
        On Error Resume Next
        xAddress = Application.ActiveWindow.RangeSelection.Address
        Set xRg  = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
        Set xRg  = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
        If xRg Is Nothing Then Exit Sub
            If xRg.Columns.Count > 1 Then
                MsgBox "You can't select multiple columns", , "Kutools for Excel"
                Exit Sub
                End If
                Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
                Set xRg1 = xRg1.Range("A1")
                If xRg1 Is Nothing Then Exit Sub
                    xUpdate = Application.ScreenUpdating
                    Application.ScreenUpdating = False
                    For Each xCell In xRg
                        xRet = Split(xCell.Value, ",")
                        xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                        I = I + UBound(xRet, 1) + 1
                    Next
                    Application.ScreenUpdating = xUpdate
                End Sub

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2022-07-29T00:24:43

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range or from within sheet
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID#", Int64.Type}, {"Lot#", type text}}),

//Split by comma into rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(
        Table.TransformColumns(#"Changed Type", {{"Lot#", 
            Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
                let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Lot#"),

//Group by ID#
//  for each ID#, add an Index column starting at 0.01 and incrementing by .01
//  then combine the index column with the ID# to generate the serialized values
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"ID#"}, {
        {"all", (t)=> let 
            index=Table.AddIndexColumn(t,"Index",.01,.01,type number),
            newID = Table.TransformRows(index, (r)=>
                Record.TransformFields(r, {"ID#", each _ + r[Index]}))
                in newID,
            type table [#"ID#"=nullable number, #"Lot#"=nullable text, Index=number]}}),
    
//Remove the original ID# column
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"ID#"}),

//re-expand the ID# and Lot# columns
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"ID#", "Lot#"})
in
    #"Expanded all"

Looking to Split the Data into Row by Delimiter

1 Answers