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

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 2

Use VLOOKUP.

_{(source: xanga.com)}

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