Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Another Compare Data columns issue this time with 2 columns

    I saw an earlier post about comparing data. That looks really helpful. But I have a slightly different (more complex?) issue.

    I have two columns of data to compare with a different two columns of data.

    Here is what I mean.

    A B
    4 27
    6 19
    7 20
    9 10


    C D
    4 27
    5 25
    6 18
    7 20

    I need to first, compare column A with Column C. I need to know what figures occur in one, but not the other.
    Where the figures in A and C do match, I need to compare the corresponding figures in B and D.

    I tried creating a column with the range of possible numbers than can occur in Columns A and C then used a VLookup function to carry the B and D values to side by side columns. I then subtracted those columns and used conditional formatting to highlight the cells where the values were not zero. That is not working well because VLookup function is returning a value from Columns B and D even when the searched for value does not occur in A or C. Also, the range of possible values in A and C are fairly large and occasionally contain alpha characters instead of the usual numeric value.

    Any help how to do this will be greatly appreciated.

    Larry P

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    When A matches C, what do you want to do when comparing B and D?

    I believe this formula (filled down) would tell you in what row in D corresponds with the value matched in B when A matches C.

    =IF(ISERROR(MATCH(A1,$C$1:$C$4,0)),"",MATCH(INDIRE CT("B"&MATCH(A1,$C$1:$C$4,0)),$D$1:$D$4,0))

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts
    What I have in mind is this. When Column A matches Column C, how do the corresponding figures in Column B compare with Column D?

    Am receiving items from the manufacturer (lots of items). Am trying to track which items have been received (by item number) and how the the batch number (different than the item number) matches with a list we have been sent.

    Call Columns A and B Our list of Item received (A) and batch number (B). Trying to compare list from the Manufacturer of what they say they made Item Number (C) and Batch Number (D).

    Hope that makes sense.

    Larry P

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    While you don't have a formula for this, could you dummy up something and attach it that would show what you're trying to accomplish?

Tags for this Thread

Posting Permissions

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