VBA help need with sorting out data

[klikeras]

Hey guys, I'd really appreciate some help with this. I'm very new to VBA scripting so I've no idea how to do this and I need it for a project.
Basically, I need to sort out a large sheet of data by moving the order/product line items into 1 column while keeping the rest of user data in that row (columns A,B,C,D,E,F,G,H,I,J,K,L).

This is basically what I need:

1) copy row, move values from columns PQR to columns MNO.
2) copy row, move values from columns STU to columns MNO
3) copy row, move values from columns VWX to columns MNO
etc.

Number of products in 1 row isn't always the same, minimum is 1 product, max is 5 products.

I probably haven't explained the issue that well so I'm attaching an image that should give you an idea of what I'm looking for.

Your help will be much appreciated!

Thanks!

 

[klikeras]

My bad, image was too big so I'm attaching it here:

 

[klikeras]

No one?

 

[snjpverma]

the Data in Columns A to L are always the same (as shown in your example)?

 

[klikeras]

the Data in Columns A to L are always the same (as shown in your example)?

Nope, that's basically customer info so it changes from customer to customer.
Also, the number of products changes as well, min is 1, max is 5.

I got this on another forum, looks like it solves the problem, so in case someone's looking for similar thing, here's the solution:

Sub zz()
Dim a, rng As Range, n&, b(), ws As Worksheet, c%
Set ws = ActiveSheet
Set rng = Rows(2).Find("Product Name")
c = rng.Column - 1
a = [a2].CurrentRegion.Value
ReDim b(1 To (UBound(a) - 1) * (UBound(a, 2) - c) / 3, 1 To c + 3)
For i = 2 To UBound(a)
    For j = c + 1 To UBound(a, 2) Step 3
        If Len(a(i, j)) > 0 Then
            n = n + 1
            For jj = 1 To c
                b(n, jj) = a(i, jj)
            Next
            For jj = 0 To 2
                b(n, c + 1 + jj) = a(i, j + jj)
            Next
        End If
    Next
Next
Workbooks.Add 1
ws.[a2].Resize(2, c + 3).Copy [a1]
[a2].Resize(1, c + 3).Copy
[a2].Resize(n, c + 3).PasteSpecial Paste:=xlPasteFormats
[a2].Resize(n, c + 3) = b
Application.CutCopyMode = 0
End Sub

 

[snjpverma]

Nope, that's basically customer info so it changes from customer to customer.
Also, the number of products changes as well, min is 1, max is 5.

I got this on another forum, looks like it solves the problem, so in case someone's looking for similar thing, here's the solution:

Sub zz()
Dim a, rng As Range, n&, b(), ws As Worksheet, c%
Set ws = ActiveSheet
Set rng = Rows(2).Find("Product Name")
c = rng.Column - 1
a = [a2].CurrentRegion.Value
ReDim b(1 To (UBound(a) - 1) * (UBound(a, 2) - c) / 3, 1 To c + 3)
For i = 2 To UBound(a)
    For j = c + 1 To UBound(a, 2) Step 3
        If Len(a(i, j)) > 0 Then
            n = n + 1
            For jj = 1 To c
                b(n, jj) = a(i, jj)
            Next
            For jj = 0 To 2
                b(n, c + 1 + jj) = a(i, j + jj)
            Next
        End If
    Next
Next
Workbooks.Add 1
ws.[a2].Resize(2, c + 3).Copy [a1]
[a2].Resize(1, c + 3).Copy
[a2].Resize(n, c + 3).PasteSpecial Paste:=xlPasteFormats
[a2].Resize(n, c + 3) = b
Application.CutCopyMode = 0
End Sub

Does this sort your data too?

 

[klikeras]

Does this sort your data too?

Yup it does, copies the row as many times as needed and puts all products in column M.
One thing that it doesn't do and I'd prefer it did, is check for double entries if Product Name + Color is the same, merge them, then update quantity.