Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Items that don't match (all)

    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
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Items that don't match (all)

    Do the product codes always match? If so, just subtract the two prices and use the custom format <pre>-$#,##0.00; $#,##0.00; </pre>

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Items that don't match (all)

    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

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Items that don't match (all)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Items that don't match (all)

    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(Sh eet2!A2,Sheet1!$A$2:$B$5,2,0),Sheet2!A2)

    Aladin
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •