Create separate row entries for multi-valued fields

Question

I have a table with a list of items. This is essentially an export from a problem tracking tool. One of the columns in this table contains comma-separated values. I am looking for a way to create separate records for the individual values ​​of multi-valued records.

Example: (this is a simplified example, the real case contains about a dozen columns)

Initial data:

ID | Title | Areas Affected | 1 | Issue title A | Area X, Area Y | 2 | Issue title B | Area Y, Area Z | 3 | Issue title C | Area X, Area Z | 

What am I trying to do:

 ID | Title | Areas Affected | 1 | Issue title A | Area X | 1 | Issue title A | Area Y | 2 | Issue title B | Area Y | 2 | Issue title B | Area Z | 3 | Issue title C | Area X | 3 | Issue title C | Area Z | 

Well, now there are duplicate entries for identifiers and captions?

Is there a formula, macro or VBA script to achieve this?

Answer 1

You need to split the rows on this column using a comma as a separator. In VBA, you have a Split () function that you can use to return an array. For the first item, just return it back to the cell where the list was. For the rest, insert a new line for each element in the array (this means that you can have n elements in this list, separated by commas), copy the entire line on this new line and put the ith value there.

Answer 2

After some reading / going through the example code, here is the answer if anyone needs to. This is the actual working code, which does not match the 1: 1 examples I posted in the question.

 Sub DataLobs() Application.ScreenUpdating = False 'Nice to have to increase the script speed. Dim wsSrc As Worksheet Dim wsDst As Worksheet Dim curRowSrc As Integer Dim curRowDst As Integer Dim ttlRows As Integer Dim splitLob() As String ' Setting initial values to start rows in source and destination ' tables, as well as the total number of rows curRowSrc = 5 curRowDst = 5 ttlRows = 10000 Set wsSrc = Worksheets("Source") 'whatever you worksheet is Set wsDst = Worksheets("Destination") 'or whatever your worksheet is called wsDst.Range("A5:F" & ttlRows).Clear ' Goes through column D in the source table ' and copies rows where the D cell is not blank ' into the destination table For curRowSrc = 5 To ttlRows If wsSrc.Range("D" & curRowSrc).Value <> "" Then ' There are some blank cells in the source table, so we are eliminating them. ' Split the cell value against the comma splitLob = Split(wsSrc.Range("D" & curRowSrc).Value, ", ") 'THIS IS WHERE @AlexandreP.Levasseur HINT COMES INTO PLAY! For i = LBound(splitLob) To UBound(splitLob) wsDst.Range("A" & curRowDst).Value = splitLob(i) wsDst.Range("B" & curRowDst).Value = wsSrc.Range("A" & curRowSrc) wsDst.Range("C" & curRowDst).Value = wsSrc.Range("C" & curRowSrc) wsDst.Range("D" & curRowDst).Value = wsSrc.Range("AC" & curRowSrc) wsDst.Range("E" & curRowDst).Value = wsSrc.Range("AE" & curRowSrc) wsDst.Range("F" & curRowDst).Value = wsSrc.Range("AD" & curRowSrc) curRowDst = curRowDst + 1 Next End If Next curRowSrc End Sub