Split Comma Delimited Strings in 2 Columns and copy to rows

Khalid NGO · Mar 23, 2018

Hello Guys, Good day,

I am trying to split comma delimited data in column K & L and copy relevant data to new rows.

for Example K3 contains 50, 50 and L3 having 997795, 993795, I want to split both cells in 2 rows, so K3 should be 50 and remaining 50 along with all data in row 3 will be copied to next row, similar case for L3.

I found a vba based solution, and also tried Power Query > Split Column > Comma > Row wise, but both solutions are working partially, i.e. for 1 column only, where as I am looking for both columns.

My actual sheet is big and having upto 13 comma delimited string. I have attached sample version with my expected output.

Any or all help is appreciated.

Regards,

Asheesh · Mar 23, 2018

Hi @Khalid NGO

I am assuming Percentage and SMS ID/SSD Code will always have equal number of instances.

Am I right?

GraH - Guido · Mar 23, 2018

Hi, Khalid NGO

Would this do?

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Teams", type text}, {"Region", type text}, {"SM", type text}, {"SM Name", type text}, {"D_Code", Int64.Type}, {"DistributorName", type text}, {"Area", type text}, {"BrickCode", type any}, {"BrickName", type text}, {"Status", type text}, {"Percentage", type text}, {"SMS ID/SSD Code", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Percentage", "SMS ID/SSD Code"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.Transpose(Table.FromList(List.Combine({{[Percentage]}, {[#"SMS ID/SSD Code"]}})))),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Custom1"

GraH - Guido · Mar 23, 2018

Sorry, with file this time, I hope it is not corrupted. I'm using an older version of PQ and I got some error messages.

Chihiro · Mar 23, 2018

Here's VBA version.

Code:

Sub Demo()
Dim tempA, x, y, resA
Dim mStr As String
Dim tempColl As New Collection
Dim i As Long, j As Long, k As Long

With Sheet1
    tempA = .Range("A3:L" & .Cells(Rows.Count, 1).End(xlUp).Row).Value2
End With
For i = 1 To UBound(tempA)
    mStr = ""
    For j = 1 To 10
        mStr = IIf(Len(mStr) = 0, tempA(i, j), Join(Array(mStr, tempA(i, j)), ","))
    Next
    If InStr(tempA(i, 11), ",") Then
        x = Split(tempA(i, 11), ",")
        y = Split(tempA(i, 12), ",")
        For k = 0 To UBound(x)
            tempColl.Add mStr & "," & x(k) & "," & y(k)
        Next
    Else
        tempColl.Add mStr & "," & tempA(i, 11) & "," & tempA(i, 12)
    End If
Next
ReDim resA(1 To tempColl.Count, 1 To 12)
For i = 1 To tempColl.Count
    x = Split(tempColl(i), ",")
    For j = 0 To UBound(x)
        resA(i, j + 1) = x(j)
    Next
Next
Sheet2.Range("A2").Resize(UBound(resA), 12) = resA
End Sub

Asheesh · Mar 23, 2018

You already have a couple of solutions. Since I gave it a shot so sharing it. Below is what I came up with.

Note: The below snippet is not as efficient as suggested by @Chihiro

Code:

Option Explicit

Sub test()

Dim LastRow As Long
Dim RowIndex As Long
Dim CopyRng As Range
Dim Events As Integer, LenWODelim As Integer, LenFull As Integer, InsertRow As Integer
Dim StrPercent As String, StrSMSID As String, pSplitSub As String, sSplitSub As String


ThisWorkbook.Worksheets("Sheet1").Select

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For RowIndex = LastRow To 3 Step -1

  ThisWorkbook.Worksheets("Sheet1").Select

  LenFull = Len(Cells(RowIndex, 12))
  
  LenWODelim = Len(Application.WorksheetFunction.Substitute(Cells(RowIndex, 12), ",", ""))
  
  Events = LenFull - LenWODelim + 1
  
  Set CopyRng = Range("A" & RowIndex & ":J" & RowIndex)
  
  StrPercent = Range("K" & RowIndex).Value
  
  StrSMSID = Range("L" & RowIndex).Value
  
  ThisWorkbook.Worksheets("Output").Select
  
  Range("A2").Select
  
  For InsertRow = 1 To Events
  
  Range("A2").EntireRow.Insert
  
  CopyRng.Copy Destination:=ThisWorkbook.Worksheets("Output").Range("A2")
  
  pSplitSub = Application.WorksheetFunction.Rept(" ", Len(StrPercent))
  
  sSplitSub = Application.WorksheetFunction.Rept(" ", Len(StrSMSID))
  
  ActiveCell.Offset(, 10).Value = _
  Trim(Right(Application.WorksheetFunction.Substitute(StrPercent, ",", pSplitSub), Len(StrPercent)))
  
  On Error Resume Next
  
  StrPercent = Left(StrPercent, Len(StrPercent) - Len(ActiveCell.Offset(, 10)) - 2)
  
  On Error GoTo 0
  
  ActiveCell.Offset(, 11).Value = _
  Trim(Right(Application.WorksheetFunction.Substitute(StrSMSID, ",", sSplitSub), Len(StrSMSID)))
  
  On Error Resume Next
  
  StrSMSID = Left(StrSMSID, Len(StrSMSID) - Len(ActiveCell.Offset(, 11)) - 2)
  
  On Error GoTo 0
  
  Next InsertRow

Next RowIndex

End Sub

Khalid NGO · Mar 23, 2018

Asheesh said:
I am assuming Percentage and SMS ID/SSD Code will always have equal number of instances.
Am I right?

Hi Asheesh,
Yes you are right.

Khalid NGO · Mar 23, 2018

Hi to all,
Guys you are amazing, that was quick.

I will check all posted solutions with source data, and will give my feedback.

Regards,

Hareesh_KLD · Mar 23, 2018

Code:

Sub CopyValues()
    Dim r As Range
    Dim a As Variant
    Dim b As Variant
    Dim i As Integer
   
    For Each r In Range("A3", Range("A3").End(xlDown))
   
      a = Split(r.End(xlToRight), ",")
      b = Split(r.End(xlToRight).Offset(0, -1), ",")
     
      For i = LBound(a) + 1 To UBound(a) + 1
     
        Range(r, r.End(xlToRight).Offset(0, -2)).Copy
       
            If Sheet3.Range("A2").Value = "" Then
           
                Sheet3.Range("A2").PasteSpecial
                Sheet3.Range("A2").End(xlToRight).Offset(0, 1).Value = b(i - 1)
                Sheet3.Range("A2").End(xlToRight).Offset(0, 1).Value = a(i - 1)
            Else
               
                Sheet3.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
                Sheet3.Range("A1").End(xlDown).End(xlToRight).Offset(0, 1).Value = b(i - 1)
                Sheet3.Range("A1").End(xlDown).End(xlToRight).Offset(0, 1).Value = a(i - 1)
            End If
     
      Next i
       
    Next r
Application.CutCopyMode = False
Range("A1").Select
End Sub

Marc L · Mar 24, 2018

Hi !

Khalid, sorry if I'm a bit late but just try this tiny VBA way
[here Sheet2 codename is Worksheets("Output") …] :

Code:

Sub Demo1()
     Sheet2.UsedRange.Offset(1).Clear
     Application.ScreenUpdating = False
     L& = 2
With Sheet1.[A2].CurrentRegion.Rows
    For R& = 2 To .Count
        VK = Split(.Cells(R, 11).Value, ", ")
        VL = Split(.Cells(R, 12).Value, ", ")
     If UBound(VK) = UBound(VL) Then
        N& = UBound(VK) + 1
        .Item(R).Copy Sheet2.Cells(L, 1).Resize(N)
         Sheet2.Cells(L, 11).Resize(N, 2).Value = Application.Transpose(Array(VK, VL))
         L = L + N
     End If
    Next
End With
     Sheet2.UsedRange.Columns(12).NumberFormat = "General"
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !

Khalid NGO · Mar 24, 2018

Hi Marc & Chihiro,

Both solutions are working great and doing the job in no time.
Thank you so much, Have a good day.
@Marc, your code is much shorter, and you are on time as always

Hi Asheesh and Hareesh,
Thank you guys. Both codes are working. I appreciate your time and help.
Take Care,

Regards,

Khalid NGO · Mar 24, 2018

GraH - Guido said:

Would this do?

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Teams", type text}, {"Region", type text}, {"SM", type text}, {"SM Name", type text}, {"D_Code", Int64.Type}, {"DistributorName", type text}, {"Area", type text}, {"BrickCode", type any}, {"BrickName", type text}, {"Status", type text}, {"Percentage", type text}, {"SMS ID/SSD Code", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Percentage", "SMS ID/SSD Code"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.Transpose(Table.FromList(List.Combine({{[Percentage]}, {[#"SMS ID/SSD Code"]}})))),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Custom1"

Hi G,

Fantastic, that did the trick much faster.

I can see you have added custom column. Can you please explain your steps in Plain English (just for my learning purpose), as I am new to PQ and I am not getting this M language.

Thank you so much and have a great day.

Regards,

GraH - Guido · Mar 24, 2018

Hi Khalid,
My pleasure to #share

.
It is not too difficult what I did, though I nested a couple of functions into one. You can also do that in 3 steps via add custom column and use the M-functions. Meaning you must be typing the formulas.

List.Combine ( { { [Percentage] } , { [#"SMS ID/SSD Code"] } } )
- merges in fact both columns, which contain a list, hence the internal curly braces used
- result is again a list (external curly braces)
Table.FromList ( Table.FromList(List.Combine({{[Percentage]}, {[#"SMS ID/SSD Code"]}}) )
- Simply converts the list of lists to a table object (x-rows, y-columns), to allow a transpose
- in your case x and y are the same number, being the number of arguments in the initial lists
Table.Transpose ( Table.FromList(List.Combine({{[Percentage]}, {[#"SMS ID/SSD Code"]}})) )
- To transpose the lists of both columns, which you actually required to do

In the last step of the query, this table is simply expanded, thus duplicating the rows of the columns preceding the custom column. And it creates 2 columns for [Percentage] and [SMS ID/SSD Code] again.

Like you said, job gets nicely done. I hope herewith I was able to explain clearly enough how it is working.

Enjoy the weekend.

Khalid NGO · Mar 24, 2018

Hi G,
Thank you so much for the explanation of these M Functions. PQ is so powerful tool, I will do more research on it.

The more I use (PQ / Excel), the more I learn. The more I learn, the more I realize, the less I know.

@ all,
Please accept my sincere gratitude for your help.

Regards,

Split Comma Delimited Strings in 2 Columns and copy to rows

Khalid NGO

Excel Ninja

Attachments

Asheesh

Excel Ninja

GraH - Guido

Well-Known Member

GraH - Guido

Well-Known Member

Attachments

Chihiro

Excel Ninja

Asheesh

Excel Ninja

Attachments

Khalid NGO

Excel Ninja

Khalid NGO

Excel Ninja

Hareesh_KLD

Member

Attachments

Marc L

Excel Ninja

Khalid NGO

Excel Ninja

Khalid NGO

Excel Ninja

GraH - Guido

Well-Known Member

Khalid NGO

Excel Ninja