Looking to Split the Data into Row by Delimiter-CodePudding

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

CodePudding user response：

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"

CodePudding user response：

Example VBA approach:

Sub Tester()
    Dim rw As Range, c As Range, arr, i As Long, ws As Worksheet
    
    Set ws = ActiveSheet
    Set c = ws.Range("F2") 'starting point for output
    
    For Each rw In ws.Range("A2:B13").Rows        'loop over rows of input data
        arr = Split(rw.Cells(2).Value, ",")       'split ColB value to array
        For i = 0 To UBound(arr)                  'loop over array
            c.Value = rw.Cells(1).Value & "." & Format(i   1, "00")
            c.Offset(0, 1).Value = Trim(arr(i))
            Set c = c.Offset(1)                   'next output row
        Next i
    Next rw
End Sub