I have a Microsoft Excel worksheet that has hundreds of rows of items. The category column in this items worksheet is filled with category names, which are duplicated since it's a many-to-one relationship of items to categories. I would like to replace these category strings with IDs from a second worksheet that has a unique list of the category names paired with IDs. So, the common link between the two is the category name.
我有一个Microsoft Excel工作表，有数百行项目。此项目工作表中的类别列填充了类别名称，这些类别名称是重复的，因为它是项目与类别的多对一关系。我想用第二个工作表中的ID替换这些类别字符串，该工作表具有与ID配对的类别名称的唯一列表。因此，两者之间的共同联系是类别名称。
How can I do this in Microsoft Excel? Does it involve making a vba macro?
我怎么能在Microsoft Excel中执行此操作？它是否涉及制作vba宏？
3 个解决方案
#1
4
First off, as the comment suggests, I think a good idea wouls be to move this to a database, even MS Access will suffice. Lets say you can not do that, so business rule that you require this to saty in excel.
首先，正如评论所暗示的那样，我认为一个好主意是将它移到数据库中，即使MS Access也足够了。让我们说你不能这样做，所以业务规则要求你在excel中做得很好。
OK, so lets start.
好的，让我们开始吧。
- From the sheet with the Items data, copy the entire column containing the Category strins, and paste this into a new sheet.
- 从包含Items数据的工作表中，复制包含Category strins的整个列，并将其粘贴到新工作表中。
- Use the Remove duplicates command to ensure that you only have unique values in this new sheet.
- 使用“删除重复项”命令可确保在此新工作表中只有唯一值。
- Now, to the right (must be right, we will use this in a VLOOKUP later) of this new column, create a column that contains 1..N, simulating Autonumber.
- 现在，在这个新列的右边（必须是正确的，我们将在后面的VLOOKUP中使用它），创建一个包含1..N的列，模拟自动编号。
- Now on the original sheet, insert a new column to the right of the current Category column. Use VLOOKUP to retrieve the values from the Autonumber sheet. Something like this
=VLOOKUP(A1,Sheet1!$D$1:$E$6,2) - 现在，在原始工作表上，在当前“类别”列的右侧插入一个新列。使用VLOOKUP从“自动编号”工作表中检索值。像这样的东西= VLOOKUP（A1，Sheet1！$ D $ 1：$ E $ 6,2）
Now after you have checked that all your IDs seem correct, you can remove the Category column from the Items sheet if you wish so.
现在，在检查了所有ID看起来是否正确之后，如果您愿意，可以从“项目”表中删除“类别”列。
I would still recomend using a database for this X-)
我仍然建议使用数据库为这个X-）
#2
3
Use VLOOKUP.
使用VLOOKUP。
(You have to sort the category names for it to work.)
（您必须对类别名称进行排序才能生效。）
#3
0
You need one of the LOOKUP functions. Here's an article on this topic from Microsoft. And this is another one with more details and examples.
您需要一个LOOKUP功能。这是Microsoft的一篇关于此主题的文章。这是另一个有更多细节和例子的人。
Note that using LOOKUP you'll be able to create a new column with the IDs next to the category names. If this is a one-off operation, you can then just replace the category column with that new ID column.
请注意，使用LOOKUP，您将能够创建一个新列，其中包含类别名称旁边的ID。如果这是一次性操作，则可以只使用该新ID列替换category列。
#1
4
First off, as the comment suggests, I think a good idea wouls be to move this to a database, even MS Access will suffice. Lets say you can not do that, so business rule that you require this to saty in excel.
首先，正如评论所暗示的那样，我认为一个好主意是将它移到数据库中，即使MS Access也足够了。让我们说你不能这样做，所以业务规则要求你在excel中做得很好。
OK, so lets start.
好的，让我们开始吧。
- From the sheet with the Items data, copy the entire column containing the Category strins, and paste this into a new sheet.
- 从包含Items数据的工作表中，复制包含Category strins的整个列，并将其粘贴到新工作表中。
- Use the Remove duplicates command to ensure that you only have unique values in this new sheet.
- 使用“删除重复项”命令可确保在此新工作表中只有唯一值。
- Now, to the right (must be right, we will use this in a VLOOKUP later) of this new column, create a column that contains 1..N, simulating Autonumber.
- 现在，在这个新列的右边（必须是正确的，我们将在后面的VLOOKUP中使用它），创建一个包含1..N的列，模拟自动编号。
- Now on the original sheet, insert a new column to the right of the current Category column. Use VLOOKUP to retrieve the values from the Autonumber sheet. Something like this
=VLOOKUP(A1,Sheet1!$D$1:$E$6,2) - 现在，在原始工作表上，在当前“类别”列的右侧插入一个新列。使用VLOOKUP从“自动编号”工作表中检索值。像这样的东西= VLOOKUP（A1，Sheet1！$ D $ 1：$ E $ 6,2）
Now after you have checked that all your IDs seem correct, you can remove the Category column from the Items sheet if you wish so.
现在，在检查了所有ID看起来是否正确之后，如果您愿意，可以从“项目”表中删除“类别”列。
I would still recomend using a database for this X-)
我仍然建议使用数据库为这个X-）
#2
3
Use VLOOKUP.
使用VLOOKUP。
(You have to sort the category names for it to work.)
（您必须对类别名称进行排序才能生效。）
#3
0
You need one of the LOOKUP functions. Here's an article on this topic from Microsoft. And this is another one with more details and examples.
您需要一个LOOKUP功能。这是Microsoft的一篇关于此主题的文章。这是另一个有更多细节和例子的人。
Note that using LOOKUP you'll be able to create a new column with the IDs next to the category names. If this is a one-off operation, you can then just replace the category column with that new ID column.
请注意，使用LOOKUP，您将能够创建一个新列，其中包含类别名称旁边的ID。如果这是一次性操作，则可以只使用该新ID列替换category列。