Thread: Comma Delim to Rows - Everything Else Copied

Hey Guys/Girls,

I'm a first time poster, so please bear with me if I am not 100% spot on with my question.

I have a spreadsheet of data that contains multiple item numbers (one per row) and one column in that list has comma-delim data (2 characters).

It's currently taking 3 employees 2 whole days to sift through this report in order to extract what they need. If we are able to take this data (attached) and blow it out so that we create separate rows for each of the Product Code, it would save a ton of work.

To summarize, I need a way to take all of the comma-delim 2-digit codes in Column O and make them go to one in each row, but also copy all of the data from the row it's in to the single row. In the current example, Row 2 would not change, then Row 3 would be Item 1234 and Product CodeAB along with everything else in the current row copied to that row. Then, Row 4 would be Item 1234 and Product Code AC with everything else in the current row copied to that row.

Finally, the actual data set is 2,455 rows long before I would run the macro so the result could potentially be 400-500,000 rows of data. Also, I would need all columns to be copied over since all columns are used at some point in the data.

P.S. I would like the macro to create the result in a new tab if at all possible....but it's not 100% required.

Thanks so much to whoever can provide assistance.

Take care.

Try this.

Sub Test()
Dim TargetSheet As Worksheet
Dim N As Long
Dim M As Long
Dim ProductCodeArray
Sheets.Add
Set TargetSheet = ActiveSheet
Sheets("Sheet1").Activate
Rows(1).Copy Destination:=TargetSheet.Rows(1)
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    ProductCodeArray = Split(Cells(N, 15), ",")
    For M = 0 To UBound(ProductCodeArray)
        Rows(N).Copy Destination:=TargetSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        TargetSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 14) = ProductCodeArray(M)
    Next M
Next N
TargetSheet.Activate
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

Hi JMcDon1007

In Excel 2007, try saving the file as an xlsm (macro enabled) file. See if that helps. In reading the code as posted, it doesn't appear to require modification.