Find records where range has changed - Excel

[sncb]

Hi there,

Not sure if this would be the right place to ask but hopefully I get a solution.

I have orders (hundreds of lines) in excel where based on a certain postal code range the salesperson was assigned his/her margin. The issue I face is that the zip code range has changed and I need to extract only those order records where the new zip code of the Salesperson falls outside the existing range.

So in my list of orders, I need to identify the rows shown in yellow(highlight, extract, or any other option), as these are now in the new range. Any idea how I should go about this?

 

[Joe4]

You should just be able to do Conditional Formatting, highlighting all rows where column K is not equal to column L.
That Conditional Formatting Formula would look like:

=$K2<>$L2

 

[sncb]

Thanks Joe,

Maybe I should have been more clear. The only info I have is this below. I was using col K and L to indicate my expected rows.

 

[Joe4]

As long as there are no gaps in your zip codes, and your zip code lookup tables are sorted in order by the first column, you can get the values in columns K and L using VLOOKUP function with the 4th argument ("approximate match") set to "TRUE". See: Excel VLOOKUP Function Examples and Troubleshoot Problems

So, there are two ways would go about it.
You could create two VLOOKUP formulas:
- column K: VLOOKUP using original (old) lookup table
- column L: VLOOKUP using new lookup table

And then do the Conditional Formatting I explained in my first reply.

Or, you could skip the "helper" columns and do the VLOOKUP formulas right in the Conditional Formatting formula, i.e.

=VLOOKUP(old)<>VLOOKUP(new)

 

[sncb]

Thank you Joe. I will try this out tomorrow and let you know.

 

[sncb]

Hit a bump. Just realized that the new zip code range has gaps and not continuous. The data looks more like this. Any suggestions?

 

[Joe4]

I would probably create "dummy" recrods to fill the gaps. If you do that, then you can still probably use the same methodology. i.e.

Otherwise, you are going to need a much more complex formula or possibly have to use VBA.

 

[sncb]

Brilliant idea. I'll try that and report back. Thanks

 

[sncb]

Hi Joe,

Just wanted to give you feedback that your solution worked spot on. I did have to do some data crunching myself based on what I needed but your crucial input helped to think in the right direction and finally resolve the issue. So thanks a ton.

 

[Joe4]

You are welcome.
Glad I was able to assist.