I've been stuck for a while trying to find the best way to perform this task through vba and figured I'd give it a try here.
I was trying to find the best way to do the following in the attached workbook:
1. index/match all of the prices for the serial numbers on sheet1 from the produce tab; (assuming you put the values in column2 on sheet1)
2. For any remaining serial numbers that did not have a price (N/A), lookup the prices for those serial numbers using the alternate tab. Please note the alternate tab has 2 sets of prices that must be referenced when looking up the remaining serial numbers (columns C:H - prices and types will always be identical) (assuming you put the values in column3 for the first set of prices and column 4 for the second set of prices on sheet1)
3. Then index/match the 'type' (rice, eggs, etc.) on the alternate tab (eg rice, lettuce etc) for ALL and ONLY serial numbers that are purple on sheet1 (assuming you put the values in column 5 on sheet1)
4. for any items that do not have a price (columns 2:4) at this point, clear contents (do not delete row) up until column 9 (do not clear contents in column 10)
5. For any serial numbers that have either a price, or price and security type (in columns 2:5), Paste special values into columns 8 & 9 (price and type columns) on sheet1
6. clear all index/match values in col. 2:5
Please note that the number of rows will always be dynamic and changing so the number of different colored rows will always be different.
I have attached the workbook for your reference, any and all help is greatly appreciated in advance!