Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a spreadsheet of product information in Sheet 1. Each product has a unique ID code. However, I need to update all occurenced of the unique product ID with a new code that has been generated for each one. So, I have a lookup table that lists 'old product ID' and 'new product id' in Sheet 2

Is it possible with VB to loop through the lookup list in Sheet2 and replace all occurences in the sheet 1?

E.g. Sheet 1

Product ID   |   Name     |  Category   |  Related Product  |
0001             t-shirt     10            005, 987
0002             shorts      12            009, 004 

Sheet 2

old product ID  |  new product ID
0001                TEE_1
0002                SHRT_1
009                 SOCK_1

So you will see, that where a value is found in column ' old product ID' it needs to be replaced with the value in column 'new product ID'

Thanks for any advice!

share|improve this question

migrated from Mar 4 '13 at 21:53

This question came from our site for professional and enthusiast programmers.

You can simply use a vlookup function. The below would work assuming your sheet2 data starts from cell A1. Also make sure product ID feild are of text format.

enter image description here

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.