Thread: Another Compare Data columns issue this time with 2 columns

1. 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. 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. 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. 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?