Complicated (for me) Lookup or Compare

[LCTrucido]

Hey folks,

I've got another one for the genius residents of this fine forum. At work I run a report that creates an excel file. I've already created a master with the VBA required to clean it up so the process will be run the query, paste into the master, then run the macros and save the results.

In the master there will be a sheet with a list of SKU's in column B. In columns E - Z there are possible location codes where those SKU's are supposed to be stored, but different SKU's will have different amounts of possible locations. Some may have one, others may use the full range. In either case, each row will contain a unique SKU identifier followed by some number of location codes. This sheet will not change is and used for reference. Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Grade[/TD]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD]Name[/TD]
[TD]Location1[/TD]
[TD]Location2[/TD]
[TD]Location3[/TD]
[/TR]
[TR]
[TD]Gold[/TD]
[TD]123456[/TD]
[TD]BlahBlah1[/TD]
[TD]Blah1[/TD]
[TD]100.A.1[/TD]
[TD]100.A.2[/TD]
[TD]100.A.3[/TD]
[/TR]
[TR]
[TD]Silver[/TD]
[TD]234567[/TD]
[TD]BlahBlah2[/TD]
[TD]Blah2[/TD]
[TD]200.A.1[/TD]
[TD]200.A.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bronze[/TD]
[TD]345678[/TD]
[TD]BlahBlah3[/TD]
[TD]Blah3[/TD]
[TD]300.A.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


When I export my data and trim it, it will show an itemized list of barcodes, the SKU of the item, and the current location code. What I'm trying to do is add a formula or VBA to the data dump to reference the above sheet and say whether or not a line item in the report is in an allowed location. Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Barcode[/TD]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD]Current Location[/TD]
[TD]Location Good?[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]123456[/TD]
[TD]BlahBlah1[/TD]
[TD]100.A.2[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]234567[/TD]
[TD]BlahBlah2[/TD]
[TD]200.A.3[/TD]
[TD]Nope[/TD]
[/TR]
[TR]
[TD]30000[/TD]
[TD]345678[/TD]
[TD]BlahBlah3[/TD]
[TD]300.A.1[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Thoughts?

 

[inactiveuserps07]

@LCTrucido, what are the ranges (including headers) of the two above grids? Or, if they are formatted as tables, what are the table names?

 

[LCTrucido]

@LCTrucido, what are the ranges (including headers) of the two above grids? Or, if they are formatted as tables, what are the table names?

Each table is symbolic of the format of their respective and separate worksheets. Assume A1 for the first header label. The master list that doesn't change is in a worksheet called SKU INFO, the data dump sheet is DATA.

 

[inactiveuserps07]

In DATA!E2 and copied down as far as needed:

=IF(ISNUMBER(MATCH(D2,OFFSET('SKU INFO'!$E$1,MATCH(B2,'SKU INFO'!B$2:B$5000,0),0,1,22),0)),"Yes","Nope")

Adjust the B$2:B$5000 bottom range to accommodate the max number of rows in your actual 'SKU INFO'! sheet.

(Though it'd be more flexible if you formatted each range as an official table.)

 

[LCTrucido]

It functions, but not quite as expected. I'm out of my depth on the Match and Offset but using the data above the first and third entries should return positive results since their current locations are withing the allowed locations for those SKU's. The second would be negative as the current location is not on the list.

This formula returns negative on all three entries.

 

[LCTrucido]

If Current Location for a SKU in DATA matches any location for that SKU in SKU INFO, return Yes. If not, Nope.

 

[inactiveuserps07]

Works perfectly here. I'd need to know more in order to help you troubleshoot it.

 

[LCTrucido]

Works perfectly here. I'd need to know more in order to help you troubleshoot it.

You're absolutely right. I don't know what I did wrong at home but it's working perfectly here at the office.

Thank you!

 

[inactiveuserps07]

@LCTrucido, it's possible that your office uses an older version of Excel that doesn't support ISNUMBER(). If you'd like to post your office version of Excel here, I or someone else can adapt the formula to work with the version you have.

 

[LCTrucido]

@LCTrucido, it's possible that your office uses an older version of Excel that doesn't support ISNUMBER(). If you'd like to post your office version of Excel here, I or someone else can adapt the formula to work with the version you have.

I think that was the issue at home, I have an older version there. Here at the office with 2013 it's working great.