Create New Unique ID Based on Two Columns - Excel

Question

I have a problem at work where we would take an old SKU number and replace it with a new one. Unfortunately, there are instances where the new SKU number replacing an old SKU number would eventually become an 'old' SKU itself and would be phased out. Below is an example.

    Old Sku   New SKU
    06223     34162
    06223     34162
    06553     01925
    06557     19100
    06557     19100
    06573     11443
    06573     11443
    51095     06223
    51095     06223

With the way I need it formatted for work, I need the three different SKU's to become 1 unique SKU, so 06223, 34162, and 51095 would equal a new SKU # of 12345.

This is what I would need it to look like

    Old Sku New SKU Unique SKU
    06223   34162   1
    06223   34162   1
    06223   34162   1
    06553   01925   2
    06557   19100   3
    06557   19100   3
    06573   11443   4
    06573   11443   4
    51095   06223   1
    51095   06223   1

I am not too familiar with the indirect function but I have been told I may need to use that. I appreciate all the help. Thank you!

EDIT @CallumDA this is what I am getting with your code

    Old SKU         New SKU         All New SKU
    00080           00080           1
    00606           24264           2
    00606           98952           3
    00644           16814           4
    00721           58008           5
    00753           01929           6

Rows 2 and 3 should have 2 in the all new sku

Why do you need a third unique SKU. Could you just use the "New SKU"? Which would be `34162` for the last record, for example — CallumDA, Feb 06 '17 at 16:08
I have to have it that way because I need to create a forecast model and since the three SKUs are the same product, I need to forecast them as the same item and not three separate items. — Drew, Feb 06 '17 at 16:11
@CallumDA I forgot that I had left that in there. Please ignore that as I will be deleting rows where 0000 is in the New SKU column. Sorry for the mistake — Drew, Feb 06 '17 at 16:27
@CallumDA I am still getting this with your code if you take a look at rows 2 and 3. The new sku should be 2 for both. Not sure what is going wrong. ' Phase Out MSPN Phase In MSPN All New SKU 00080 00080 1 00606 24264 2 00606 98952 3 00644 16814 4' — Drew, Feb 06 '17 at 17:03
I can't really tell from that comment. If your data is structured slightly differently, can you update the question just to show which columns your relevant data will be in — CallumDA, Feb 06 '17 at 17:09
@CallumDA I will show you the table of what I am getting in the original question. I really appreciate your help by the way. I'm sure we will fix this soon — Drew, Feb 06 '17 at 17:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134985/discussion-between-callumda-and-drew). — CallumDA, Feb 06 '17 at 17:18

CallumDA · Accepted Answer · 2017-02-06T17:52:19.293

Place this formula into C2 and drag down

=IFERROR(VLOOKUP(IFERROR(VLOOKUP(B3,$A$1:$B1,2,0),B2),$B$1:$C1,2,0),MAX($C$1:C1)+1)

Which looks like this:

As I was suggesting in the comments, you might also be fine with a simpler solution:

=IFERROR(VLOOKUP(B2,$A$2:$B$11,2,0),B2)

Which just gets the latest New SKU -- like this: (in the "Alternative" column)

Update

Since you updated your data you have a one-to-many relationship as well as many-to-one. Here is the updated method and formula. I changed the value in cell B3 for this scenario and split it into two columns for ease:

The formula in D2 is:

=VLOOKUP(INDEX($A$2:$A$11,MATCH(IFERROR(VLOOKUP(B2,$A$2:$B$11,2,0),B2),$B$2:$B$11,0)),$A$2:$B$11,2,0)

And likewise for E2 is:

=IFERROR(VLOOKUP(D2,$D$1:$E1,2,0),MAX($E$1:E1)+1)

Your updated data now looks like this:

Create New Unique ID Based on Two Columns - Excel

1 Answers1

Linked