VBA Multiple Nested IF functions within table

[freya306]

So I've got a big stock spreadsheet with multiple tabs to seperate catergories and within each tab is a table. each containing items, each with multiple sizes and then the quantity etc.
for example one group of items I have is

[TABLE="width: 368"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Item
[/TD]
[TD]Code
[/TD]
[TD="align: right"]Size
[/TD]
[/TR]
[TR]
[TD]Staff Cardigan
[/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]Staff Cardigan[/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Staff Cardigan[/TD]
[TD][/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]Staff Cardigan[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Staff Cardigan[/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]Staff Cardigan[/TD]
[TD][/TD]
[TD="align: right"]46[/TD]
[/TR]
</tbody>[/TABLE]

I want the spreadsheet to create a 'code' for each item depending on the item name and size for example for the first item the code would be STFC34.
I can write a single IF function in one cell to calculate that but I have over 500 items on 6 different sheets that I want it to dictate. So is there a VBA or something that I can write that it can generate the code dependant of the item and size.
The if code I have for a single cell is:
=IF(AND(Table1[@Item]="Staff Cardigan",Table1[@Size]=34),"STFC34",IF(AND(Table1[@Item]="Staff Cardigan",Table1[@Size]=36),"STFC36"))
obviously this would work for the first two lines but I would like to write something that could do the whole table in one go!
Please Help! I havent used VBA before so if someone could help to write one if function for the first line then I can copy it to input the codes for each item I want to use.

 

[Eric W]

I'd recommend creating another sheet with the basic translate table. Name it "Translate". Then in column A put the description, and in B put the code. For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Staff Cardigan[/TD]
[TD]STFC[/TD]
[/TR]
[TR]
[TD]Button Sweater[/TD]
[TD]BTNS[/TD]
[/TR]
[TR]
[TD]Long Pants[/TD]
[TD]LNGP[/TD]
[/TR]
</tbody>[/TABLE]


and so on. Then you can use a VLOOKUP function to create the codes. If your Item column is A, Code is B, and Size is C, it would be:

=VLOOKUP(A1,Translate!$A:$B500,2,FALSE)&C1

Copy that formula down column B on each of your sheets.

 

[freya306]

I'd recommend creating another sheet with the basic translate table. Name it "Translate". Then in column A put the description, and in B put the code. For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Staff Cardigan[/TD]
[TD]STFC[/TD]
[/TR]
[TR]
[TD]Button Sweater[/TD]
[TD]BTNS[/TD]
[/TR]
[TR]
[TD]Long Pants[/TD]
[TD]LNGP[/TD]
[/TR]
</tbody>[/TABLE]


and so on. Then you can use a VLOOKUP function to create the codes. If your Item column is A, Code is B, and Size is C, it would be:

=VLOOKUP(A1,Translate!$A:$B500,2,FALSE)&C1

Copy that formula down column B on each of your sheets.

Thanks Eric! Very Helpful! now just to create hundreds of unique Codes!