Transpose based on duplicate Order Numbers

[rmorris18921892]

Dear All,

Not overly pressing to be answered quickly, but something that is frustrating me.

I have line item for orders placed in Q! & Q2 of 2016. Each line item has the order date, date ordered, SKU (warehouse ID code), (for context on what I am working on) the last two characters of the SKU include the size of the product.

Due to the obvious nature of these being line items, if a customer orders more than one product, I now have duplicates of the same order number on differing rows and thus the SKUs are not paired. I need the SKUs to be in a string in one cell, (I can then split them into differing cells on the same row) that attributes to the first order number in my sheet.

I would like SH-QWER-LB-AA & SH-ABCD-DN-AA to appear in "column D" (to be created...), row 2, row 3 can include the same data (I'd then simply find the unique values...thus eradicating the duplicate). Row 4 would just show the one SKU.


Quick example:-
A B C

Order #	Email	Lineitem sku
#45002	TTT@gmail.com	SH-QWER-LB-AA
#45002	TTT@gmail.com	SH-ABCD-DN-AA
#44029	ryan@gmail.com	CHAL-N-AB
#43978	leeds@gmail.com	TE-POCK-WH-TB

<colgroup><col style="width: 100px"><col width="100"><col width="131"></colgroup><tbody>
</tbody>

I hope I've been descriptive enough for what I'm after.

Many thanks,
Ryan

 

[NotoriousPopol]

Re: Transpos based on duplicate Order Numbers

Some VBA would be Ok?

You could go with something like this:

Sub RetrieveData()


Dim L As Integer, LEnd As Integer, str As String, i As Integer, Id As String


LEnd = Range("A10000").End(xlUp).Row ' last row used


For L = 2 To LEnd - 1
    str = Cells(L, 3) ' we store the first code
    Id = Cells(L, 1) ' we store the Id to check if we find matches
    For i = L + 1 To LEnd ' we loop through all rows under L
        If Cells(i, 1) = Id Then 'If we match, we add the code
            str = str & " & " & Cells(i, 3)
        End If
    Next i
    Cells(L, 4) = str ' we display the final code in the new column
Next L


End Sub

This code will also rewrite the sku when there is no other order. You can avoid this with a boolean=TRUE if you have at least one match, else FALSE and you don't display the result.

 

[rmorris18921892]

Re: Transpos based on duplicate Order Numbers

Thanks!

Any way of doing it without VBA?

 

[NotoriousPopol]

Re: Transpos based on duplicate Order Numbers

Is it possible to have more that 2 occurrence?

 

[rmorris18921892]

Re: Transpos based on duplicate Order Numbers

Yep. Order #45002 could have >2 SKUs, for example.

 

[NotoriousPopol]

Re: Transpos based on duplicate Order Numbers

Ok let's try this : =C2 & "" & IFERROR(" & " & VLOOKUP(A2,$A3:$D$9,4,FALSE),""). The number "9" should be AT LEAST the last row+1. Basically, for each row, it looks for the next occurrence below and add the SKU. So on the top you will have the addition of everything!

 

[rmorris18921892]

Re: Transpos based on duplicate Order Numbers

That's awesome @NotoriousPopol!

Mind explaining the gist of how that works? I have some other instances, slightly different, where I could need to use this - be great to know rather than posting here again.

Thanks again,
Ryan

 

[NotoriousPopol]

Re: Transpos based on duplicate Order Numbers

FIRST I want to warn you : YOU CANNOT DELETE DUPLICATES with this formula. You can hide them for sure, but this is dynamic. Any change will be visible automatically on other cells!

Let's start with the VLOOKUP formula. What it does is : look in the array $A3:$D$9 for the value A2 (it will look in the first column, so $A3:$A$9), then return the value of the 4th column (= D) if there is a match.

Now, imagine the last row of your data (let's say row 10 is your last data). Data is #45002 / xx@xx.com / AAA-BBB-CD. The formula in D10 says : I take AAA-BBB-CD and look to add further match. This is the last data so nothing is found : D10=C10.

In row 7, you have #45002 / xx@xx.com / EEE-FFF-GH. The formula in D7 says : I take EEE-FFF-GH and look for further match. I have a match in A10, so I add " & " D10. The result in D7 is EEE-FFF-GH & AAA-BBB-CD.

In row 4, you have #45002 / xx@xx.com / III-JJJ-KL. The formula in D4 says : I take III-JJJ-KL and look for further match. I have a match in A7, so I add " & " D7. The result in D4 is III-JJJ-KL & EEE-FFF-GH & AAA-BBB-CD.

Hope this can help you understand the process!