Replace Strings with IDs in Excel

Question

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.

How can I do this in Microsoft Excel? Does it involve making a vba macro?

Answer 1

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.

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.

Answer 2

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.

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.
• 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.
• 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)

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.

I would still recomend using a database for this X-)

Answer 3

Use VLOOKUP.

(You have to sort the category names for it to work.)