CODEWITHSUNDEEP
excelvlookup
janicebaratheon
janicebaratheon

Reputation: 999

compare two columns of data by key in separate table

I have two tables in excel, I want compare if the two min and max columns are a match, the two keys are DivApt and ID. If they match, assign yes, otherwise no.

Table 1:

A       B         C         D
DivApt  ID          MIN      MAX    
1600202 68645032054 1374     1374   
1600202 65162062750 1873     1873   
1600202 406036501   740      740

Table 2:

A       B         C         D
DivApt  ID           MIN    MAX
1600202 456224030       0   0
1600202 30142085140     0   0
1600202 60758077305     0   0

I have tried vlookup and match function and failed, any suggestion?

Upvotes: 0

Views: 1795

Answers (1)

Jerry
Jerry

Reputation: 71538

Well, if you think it is best to combine the two tables, then that's what I'll suggest.

  1. Copy/paste Table 2 under Table 1:

    enter image description here

    enter image description here

  2. Run a PivotTable (Under Insert tab), and pick the Tabular form after putting all fields into 'Row Labels':

    enter image description here

  3. Remove sub-totals:

    enter image description here

  4. And then, you can use a formula like this to do the comparison:

    enter image description here

As you can see, the PivotTable already groups the codes and when there is a dissimilarity, it will have a blank cell by default which can be used by the formula to check for differences.

The formula is:

=IF($B4<>"",IF($B5<>"","Yes",""),IF(C4<>C3,"No","Yes"))

Upvotes: 1

Related Questions