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]

In column 2 are more comma,s. So when you split both column 2 and 3 with comma's, you will have different array sizes...

 

[Moharam]

T

In column 2 are more comma,s. So when you split both column 2 and 3 with comma's, you will have different array sizes...

Then how to fix it

 

[JEC]

One suggestion

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(Replace(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

 

[Moharam]

One suggestion

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(Replace(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

Still the same Issue, It's only considering the first row. Can you please provide your email so I can mail you the entire file.