edg126
04-07-2011, 04:02 PM
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
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