Data categorizing macro

[dzhogov]

Hello all,

I am very new to VBA and apologize in advance for the 'simple' question. I am working with thousands of rows of data in a pivot table data sheet where there are 8 categories as shown:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Product ID[/TD]
[TD]Product name[/TD]
[TD]Sales Rep[/TD]
[TD]Product Category[/TD]
[TD]Product Brand[/TD]
[TD]Quantity[/TD]
[TD]Value[/TD]
[/TR]
</tbody>[/TABLE]

The data I extract from my company's software gives me all the categories except Product category and Product Brand. Every product has a unique ID, there are about ~150 products, split into 3 brands, each with different product categories (which sometimes overlap). For example

71500 - chicken wings- bone on category - Chicken Joe brand
75502 - chicken drumsticks- bone on category - Happy Chicken brand
75502 - chicken fillet - boneless category - Happy Chicken brand
76500 - breaded fillet - breaded category - Chicken Deluxe brand

What I need is a macro which, based on the product's code, fills in the Product category and Brand for me. I prefer this over highlighting all the blanks and then pasting the information from the row above with a formula, as I believe a macro would be fairly easy to create and automating this process is worth it.

I am guessing that going about this with a giant IF statement which checks each product code and assigns it a Category and Brand is not the best option, so I am open to any suggestions. Maybe some sort of validation sheet? ​Being as specific as possible would be of great help since, like I mentioned, I am new to VBA.

 

[oldbrewer]

in the first two rows what is the logic that says one is chicken joe and one is happy chicken ?

 

[dzhogov]

One company, 3 brands, occasionally overlapping product names. The idea is that the only realiable way to identify a product and assign it to a category & brand is its ID, rather than its name.

 

[oldbrewer]

so each new product you introduce, assign a brand and a unique id. Then in your first table where you have product id, use that to get category and brand...........