CODEWITHSUNDEEP
excelvba
Cody_Morton
Cody_Morton

Reputation: 33

Assign a unique number to every unique product number in excel

I have two sheets. Sheet1 has a list of products and Sheet2 has a list of products and their location. Since the product can be in multiple locations, there are a good amount of duplicates in Sheet2.

There are 26,000 products. I have assigned each product a number in Sheet1, 1-26,000. Is there a way for me to assign each number to the correct product in Sheet2?

This is a example of what I need:

The top table would be Sheet1 and the bottom table would be Sheet2, the one with duplicates.
This is what they look like right now:

Sheet1:

Sheet2:

Please let me know if I did not explain this well enough. I appreciate your help!

Upvotes: 0

Views: 421

Answers (3)

L42
L42

Reputation: 19727

If you're Item ID's are sorted the way you illustrate it, then you can simply use an IF formula:
For example you have a data as shown:

enter image description here

Enter this formula in A2:

=IF(B2=B1,IF(ISNUMBER(A1),A1,1),IF(ISNUMBER(A1),A1+1,1))

Above formula will give you what you've described.
Again, your items should be sorted so that same Item ID are in order.

Upvotes: 0

Riverside
Riverside

Reputation: 207

copy your column A in sheet 1 to column c

Then in your sheet 2 column C row 2, use =vlookup(B2,'sheet1'!B:C,2,0) (if your first item is in B2)

Drag your formula down and it should work.

Upvotes: 1

AnalystCave.com
AnalystCave.com

Reputation: 4974

Use VLOOKUP or the INDEX MATCH combo formulas in the second sheet to locate the ITEM IDs in the first worksheet

Upvotes: 0

Related Questions