基于两列创建新的唯一ID-Excel [英] Create New Unique ID Based on Two Columns - Excel

问题描述

我在工作时遇到了一个问题，我们将旧的SKU编号替换为新的SKU编号.不幸的是，在某些情况下，新的SKU编号替换了旧的SKU编号最终将最终变成旧的" SKU本身，并将被淘汰.下面是一个例子.

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

使用需要格式化的方式，我需要三个不同的SKU才能成为1个唯一的SKU，因此06223、34162和51095等于新的SKU#12345.

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.

这就是我需要的样子

    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!

编辑 @CallumDA，这就是我使用您的代码所得到的

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

第2行和第3行在所有新的sku中应该有2个

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

推荐答案

将此公式放入 C2 并向下拖动

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)

刚刚获得最新的新SKU-如下所示:(在替代"列中)

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

更新

自从更新数据以来，您具有一对多关系和一对多.这是更新的方法和公式.在这种情况下，我更改了单元格 B3 中的值，为方便起见将其分为两列:

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:

D2 中的公式为:

=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)

对于 E2 同样是:

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

---

您的更新数据现在看起来像这样:

---

Your updated data now looks like this:

这篇关于基于两列创建新的唯一ID-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！