• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identifying changed data elements between same record on 2 files

TParsons

New Member
I’m looking for a solution re comparing data elements from within common records on 2 separate Excel files. For example, if the same person with same record ID appears on two files, but the second file’s record has a different mailing address, how do we identify the variance between the 2 files?

Here’s an example:

File 1 records
ID1000 John Smith 12 Main St Topeka KS 62000
ID1001 Tommy Shaw 11 Johnson Way Omaha NE 72331
ID1002 Bob Barker 154 Edgewood Ln Billings MT 89002

File 2 records
ID1000 John Smith 12 Main St Topeka KS 62000
ID1001 Tommy Shaw 3445 Summerset Dr Omaha NE 72334
ID1002 Bob Barker 154 Edgewood Ln Billings MT 89002

How do we compare files in this example to identify that Tommy Shaw has a different address on File 2 vs File 1?

Does anyone have formula or tips to handle this type of data comparison?

Thank you in advance.
 

vletm

Excel Ninja
TParsons
Based Your given information
=IF(a<>b,"DIFF","")
where a is file 1's cell and b is file 2's cell.
Only You know - what do You have in those files?

As a new member, please, reread Forum Rules
especially How to get the Best Results at Chandoo.org
... and You'll remember - what would You do next?
 

Peter Bartholomew

Well-Known Member
If your data is held in Tables and the IDs are reasonably reliable then
= OR( XLOOKUP(Table2[@ID],Table1[ID],Table1) <> Table2[@] )
will identify the cases in which the ID exists in the other table but with mismatched fields.
= SUM(SIGN(( XLOOKUP(Table1[@ID],Table2[ID],Table2) <> Table1[@] )))
would show the number of fields that differ.
 

TParsons

New Member
TParsons
Based Your given information
=IF(a<>b,"DIFF","")
where a is file 1's cell and b is file 2's cell.
Only You know - what do You have in those files?

As a new member, please, reread Forum Rules
especially How to get the Best Results at Chandoo.org
... and You'll remember - what would You do next?
Thanks, your answer seems to work and also directed me to some other relevant answers/Excel tools.
 

TParsons

New Member
If your data is held in Tables and the IDs are reasonably reliable then
= OR( XLOOKUP(Table2[@ID],Table1[ID],Table1) <> Table2[@] )
will identify the cases in which the ID exists in the other table but with mismatched fields.
= SUM(SIGN(( XLOOKUP(Table1[@ID],Table2[ID],Table2) <> Table1[@] )))
would show the number of fields that differ.
Thanks Peter, this should work!
 
Top