Data sorting

[Moharam]

Hi Guys please help me with this, I need to sort this as per below criteria.

Data.xlsx
	A	B	C
1	Actual Data
2	Order ID	Products	Qty
3	2018000499421	D Capsules,C Cider Vinegar,AB Oil	2,1,1
4	2018000486403	D Capsules,C Cider Vinegar,AB Oil	2,1,1
5	2018000499744	S Xtreme Power - 1,C Cider Vinegar,D Capsules,AB Oil	1,1,2,1
6
7
8	Required Data
9	Order ID	Products	Qty
10	2018000499421	D Capsules	2
11	2018000499421	C Cider Vinegar	1
12	2018000499421	AB Oil	1
13	2018000486403	D Capsules	2
14	2018000486403	C Cider Vinegar	1
15	2018000486403	AB Oil	1
16	2018000499744	S Xtreme Power - 1	1
17	2018000499744	C Cider Vinegar	1
18	2018000499744	D Capsules	2
19	2018000499744	AB Oil	1
Sheet1

 

[JEC]

Please try:

I assumed the first row starts in A2. Header in A1

Sub jec()
 Dim ar, jv, sp As Variant, i As Long, j As Long, x As Long
 ar = Cells(1).CurrentRegion
 ReDim jv(2, 0)
 
 For i = 2 To UBound(ar)
    sp = Split(ar(i, 2), ",")
    For j = 0 To UBound(sp)
       ReDim Preserve jv(2, x)
        jv(0, x) = ar(i, 1)
        jv(1, x) = sp(j)
        jv(2, x) = Split(ar(i, 3), ",")(j)
        x = x + 1
     Next
 Next
 
 Cells(1, 7).Resize(x, 3) = Application.Transpose(jv)
End Sub

 

[JEC]

You can also include the header by just changing this

For i = 2 To UBound(ar)

to

For i = 1 To UBound(ar)

 

[Moharam]

Thank you So Much Guys, Its helped me a lot....!! I am grateful to you.

 

[JEC]

You’re welcome!

 

[Moharam]

Please try:

I assumed the first row starts in A2. Header in A1

Sub jec()
 Dim ar, jv, sp As Variant, i As Long, j As Long, x As Long
 ar = Cells(1).CurrentRegion
 ReDim jv(2, 0)
 
 For i = 2 To UBound(ar)
    sp = Split(ar(i, 2), ",")
    For j = 0 To UBound(sp)
       ReDim Preserve jv(2, x)
        jv(0, x) = ar(i, 1)
        jv(1, x) = sp(j)
        jv(2, x) = Split(ar(i, 3), ",")(j)
        x = x + 1
     Next
 Next
 
 Cells(1, 7).Resize(x, 3) = Application.Transpose(jv)
End Sub

Please try:

I assumed the first row starts in A2. Header in A1

Sub jec()
 Dim ar, jv, sp As Variant, i As Long, j As Long, x As Long
 ar = Cells(1).CurrentRegion
 ReDim jv(2, 0)
 
 For i = 2 To UBound(ar)
    sp = Split(ar(i, 2), ",")
    For j = 0 To UBound(sp)
       ReDim Preserve jv(2, x)
        jv(0, x) = ar(i, 1)
        jv(1, x) = sp(j)
        jv(2, x) = Split(ar(i, 3), ",")(j)
        x = x + 1
     Next
 Next
 
 Cells(1, 7).Resize(x, 3) = Application.Transpose(jv)
End Sub

Hi JEC,

Thank you for your code, but the said code is not working properly. Its not considering the QTY also not considering the lines. Please look into and help me.

Thanks

 

[JEC]

Can you post some sample data again?

 

[Moharam]

Can you post some sample data again?

Original Data
Order ID	Product Name	Qty
225219	Skygain Bulls King Lube Oil,Skygain Bulls King-10 Caps	1,1
225186	Shilajit Resin Pack of 1,Skygain Bulls King-10 Caps	1,1
225155	Skygain BullsKing Men's Supplement Capsule,Skygain Bulls King Lube Oil	1,1
225152	Skygain Bulls King Lube Oil,Skygain BullsKing Men's Supplement Capsule	1,1
225107	Skygain Bulls King Lube Oil,Skygain BullsKing Men's Supplement Capsule	1,2
225106	Skygain BullsKing Men's Supplement Capsule,Skygain Bulls King Lube Oil	1,1
225102	Nutriherbs Shilajit 60 Capsules (500 Mg),Skygain Bulls King Lube Oil	1,1
225096	Nutriherbs Ohyes Shilajit , Safed Musli & Ashwagandha - 60 Capsule (Pack Of 1),16 Again - Himalayan Raw Ayurvedic Shilajit - 15g (Natural Source of Fulvic Acid and Trace Minerals),Red Wine Vinegar 473ml,Xtreme Power Booster-15Caps	1,1,1,1

Required Data
Order ID	Product Name	Qty
225219	Skygain Bulls King Lube Oil	1
225219	Skygain Bulls King-10 Caps	1
225186	Shilajit Resin Pack of 1	1
225186	Skygain Bulls King-10 Caps	1
225155	Skygain BullsKing Men's Supplement Capsule	1
225155	Skygain Bulls King Lube Oil	1
225152	Skygain Bulls King Lube Oil	1
225152	Skygain BullsKing Men's Supplement Capsule	1
225107	Skygain Bulls King Lube Oil	1
225107	Skygain BullsKing Men's Supplement Capsule	2
225106	Skygain BullsKing Men's Supplement Capsule	1
225106	Skygain Bulls King Lube Oil	1
225102	Nutriherbs Shilajit 60 Capsules (500 Mg)	1
225102	Skygain Bulls King Lube Oil	1
225096	Nutriherbs Ohyes Shilajit , Safed Musli & Ashwagandha - 60 Capsule (Pack Of 1)	1
225096	16 Again - Himalayan Raw Ayurvedic Shilajit - 15g (Natural Source of Fulvic Acid and Trace Minerals)	1
225096	Red Wine Vinegar 473ml	1
225096	Xtreme Power Booster-15Caps	1

 

[JEC]

In the last row you miss a "1"
Should be:

1,1,1,1,1

 

[Moharam]

In the last row you miss a "1"
Should be:

1,1,1,1,1

No Its correct, There is only 4 Items