CODEWITHSUNDEEP
stringexcelvba
David Peterson
David Peterson

Reputation: 552

excel set id for each category using vba

I have on excel file with so many rows. It file contains Product Id Column and Product name column like bellow

enter image description here

I want to add product ID to these products by name using vba(something like cluster). the result should be like this:

enter image description here

note that my original excel file is the first picture

Upvotes: 0

Views: 475

Answers (2)

Dan
Dan

Reputation: 45762

If you can assume that Product name is sorted then in A2 put the value 1 and in A3 =IF(B2=B3, A2, A2 + 1) and drag down.

Upvotes: 1

Michael
Michael

Reputation: 517

Is there a particular reason you want to use VBA? This can be done relatively simply using a reference table and vlookup.

Create a table in the workbook that will list the product name and the corresponding product ID.

    Product Name    Product ID
        x              1
        y              2
        j              3

Next, enter a VLOOKUP formula into cell A2

    =VLOOKUP(B2, $G$1:$H$5, 2, 0)

Replace $G$1:$H$ with the location of the reference table you just created. This will find the product name in the table and return the corresponding ID. You can then copy the formula down the 38000 rows.

Upvotes: 1

Related Questions