create Multiple Rows per line item

[peedeebee]

Hello, I am trying to convert some data currently exported as below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]SKU[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]sku1,sku2[/TD]
[TD]$1,$2[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]sku3,sku4[/TD]
[TD]$1,$2[/TD]
[/TR]
</tbody>[/TABLE]

We need this transformed in excel into something like the below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]SKU[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]sku1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Order1[/TD]
[TD]sku2[/TD]
[TD]$2[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]sku3[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Order2[/TD]
[TD]sku4[/TD]
[TD]$2[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated!

Thanks!

 

[sheetspread]

Text to columns can put each sku and price in its own column, then attain the desired format with:

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[JoeMo]

Welcome to the forum!

Here's a macro that assumes your starting data are in cols A:C with the first header in A1. The rearranged data are in cols E:G starting in E1 as shown below.

Excel Workbook
	A	B	C	D	E	F	G
1	Order Number	SKU	Price		Order Number	SKU	Price
2	Order1	sku1,sku2	$1,$2		Order1	sku1	$1
3	Order2	sku3,sku4	$1,$2		Order1	sku2	$2
4	Order3	sku1	$2		Order2	sku3	$1
5					Order2	sku4	$2
6					Order3	sku1	$2
Sheet7

Sub RearrangeData()
Dim R As Range, c As Range, nxRw As Long, V1 As Variant, V2 As Variant
Set R = Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
Range("E:G").ClearContents
With Range("E1:G1")
    .Value = R.Rows(1).Cells.Value
    .EntireColumn.AutoFit
End With
For Each c In R.Offset(1, 0).Resize(R.Rows.Count - 1).Columns(1).Cells
    V1 = Split(c.Offset(0, 1).Value, ",")
    V2 = Split(c.Offset(0, 2).Value, ",")
    nxRw = Range("E" & Rows.Count).End(xlUp).Row + 1
    If UBound(V1) > 0 Then
        With Range("E" & nxRw)
            .Resize(UBound(V1) + 1).Value = c.Value
            For i = LBound(V1) To UBound(V1)
                .Offset(i, 1).Value = V1(i)
                .Offset(i, 2).Value = V2(i)
            Next i
'            .Offset(0, 2).Resize(UBound(V) + 1).Value = c.Offset(0, 2).Value
        End With
    Else
        Range("E" & nxRw).Resize(1, 3).Value = c.Resize(1, 3).Value
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[peedeebee]

WOW! Thank you!

 

[peedeebee]

Any chance you could provide this macro in a google sheet script?

 

[JoeMo]

WOW! Thank you!

You are welcome - thanks for the reply.

 

[JoeMo]

Any chance you could provide this macro in a google sheet script?

Sorry, I don't use google sheets.

 

[peedeebee]

Hello, I thought I would be able to edit your code with the full data set however I was not able to figure it out! Any chance you can amend for the sheet below?

https://1drv.ms/x/s!Aqskk4TK0nsEgg8-r52FIfM98Ru3

Need columns A, B, C, D to be placed on Sheet 2 in the format you provided in your last post. Am so grateful for your help!!

 

[JoeMo]

Sorry, I don't download from the web. You can use one of the tools below to post your data layout and expected results.

Excel Jeanie link: http://www.excel-jeanie-html.de/html/down_en.php
MrExcel HTML Maker link: http://www.mrexcel.com/forum/2545970-post2.html

 

[peedeebee]

Sorry about that! It’s just a link to the excel in onedrive. I didn’t think you had to download to view. I will use one of the tools you mentioned and post.