Finding Items that don’t match (all)

Topic

New Reply

How would I go about finding out which items between two lists don’t match and only display those items if there is a discrepancy, but leave the other ones alone?

In the attached simple example, there are two sheet tabs that both have product codes and prices. On the third tab I want to know which items don’t match between the two lists. Any ideas would be most appreciated.

-Kelley

Reply | Quote

Replies

Do the product codes always match? If so, just subtract the two prices and use the custom format

-$#,##0.00; $#,##0.00;  

HTH –Sam

Reply | Quote

The product codes should match, but each list may not have the exact same product codes. I’m not interested in knowing what the monetary difference is between the two, but simply come up with a list that shows which Product Codes have a discrepancy. I suppose then I would want to know the product code, and then have the two different prices from the two different prices next to it, if that’s possible.

-Kelley

Reply | Quote

Kelley,

Maybe you should play with VLOOKUP. Assume you have your procut codes in column A on Sheet1 and on Sheet2. On Sheet3 you put the following formula in cell A2 (here I assume the table you want to search is A2:B5):
=IF(ISERROR(VLOOKUP(Sheet2!A2,Sheet1!$A$2:$B$5,2,0)),Sheet2!A2;VLOOKUP(Sheet2!A2,Sheet1!$A$2:$B$5,2,0))
This formula is just an example. It returns the product code in case the product code did not match and it returns the price in case there is an exact match of product codes. To obtain this result I use the VLOOKUP function which searches the product code (in Sheet2!A2) in the first column of the table A2:B5 on sheet2. When the product code is not found, this function returns an error, which is here captured with the ISERROR function. So, when an error is returned, the IF statement returns the value in Sheet2!A2 (that is the product code that did not match), otherwise it just returns the value in column B next to the product code that matched. Hope this is clear.

Reply | Quote

Hans,

If that’s what is required, it would be more efficient to use COUNTIF instead of using VLOOKUP twice:

=IF(COUNTIF(Sheet1!$A$2:$A$5,Sheet2!A2),VLOOKUP(Sheet2!A2,Sheet1!$A$2:$B$5,2,0),Sheet2!A2)

Aladin

Reply | Quote