Solved: match / lookup to code table on different sheet

[edg126]

I have a workbook with 2 spreadsheets
1) a list of code descriptions in 1 column
ex:
2 apples @ 2.87
app @ 1.00
chers, free
a bunch of blueberries
a lot of straberrs

2) a substring filter list with products to help identify / standardize records with a sku
app|apples|54
ppl|apples|54
cher|cherries|67
ber|strawberries|68
ber|blueberries|69

Right now what I have to do is create a auto filter for contains app or ppl and then add the sku by hand, but I'd like add a column to the first spreadsheet with the first product name from spreadsheet 2 if the substring is contained in the first.

I think what I'm having problems with is making sure that this couldn't already be done with existing excel formulas. It seems like most examples I can find are with exact string matches.

Some pointers on where to begin would be very helpful.
The way I was thinking of doing it was to write a sub that would:
1) for spreadsheet 1, loop through every row
for each row, loop through spreadsheet 2 with the instr function, and if it's a match return the result of spreadsheet 2 to the second column of the first spreadsheet. Does that make sense?

If anybody has some sample code similar to this I think it might help me some.

Thanks
-Ed

[stanleydgrom]

edg126,

1) for spreadsheet 1, loop through every row for each row, loop through spreadsheet 2 with the instr function, and if it's a match return the result of spreadsheet 2 to the second column of the first spreadsheet. Does that make sense?

An array can be created from the data in worksheet to code.

Then we can use InStr to test the items in worksheet code column A, and return the SKU in column B.

Is this correct?


Have a great day,
Stan

[stanleydgrom]

edg126,

Detach/open workbook GetSKU w1 w2 SKUary InStr - edg126 - VE36952 - SDG15.xlsm and run macro GetSKU.

Have a great day,
Stan

[edg126]

Thanks for your help, it worked exactly as I wanted it to. I'm still getting used to all the shortcuts to modify excel data -- I'm usually doing things with perl, but I think I was able to follow along the code you wrote nicely.