Compare 2 sheets or ranges (multiple columns) and highlight/count mismatches

Solvap · Sep 17, 2015

Hello all,

I hope that you have some free time to help me because this post will be quite long. First of all, I hope that I'll be clear enough to explain you my problem.
FYI, I'm working on Excel 2010 and Windows 7. I'm from Belgium so, in my workbook, parameters are separated with a semicolon ";" instead of a comma ","

I would like to build a formula to get the number of rows containing mismatches errors comparing all their cells with a corresponding row on another worksheet using their leftmost cell as common key (= UniqueID) ?

Suppose a worksheets containing contacts info with 10 columns (uniqueID, firstname, lastname, address...), each row corresponds to one unique contact identified by leftmost cell of each row; UniqueID. This worksheet has to be considered as a reference or list of valid and known contacts, lets name it;"Known_Contacts".

You then get another worksheet with the same column structure and type (10 column; UniqueID, firstname, lastname, address....). Lets name it;"Imported_Contacts".
In this "Imported_Contacts" I would like to highlight new and/or modified contacts (details mismatches):

So, I've managed to build that kind of compare/mismatch check-up using conditional format. For this, I use 3 dynamic named ranges for both imported and known contacts: Header, UniqueID and whole data (3 named ranges * 2 worksheets = 6 named ranges).

Cells color in the worksheet "Imported_Contacts" change dynamically based on the UniqueID.
a) Imported UniqueIDs not found in known ones are considered as a new contacts. All details (same row) are colored in yellow and ready to be copy-pasted under the last known contact in the Known_Contacts worksheet to be available for next update.
b) Imported UniqueIDs that are known but have mismatches are highlighted in red and all mismatches (on the same row) in orange for further investigation. They will overwrite values in the Known_Contacts worksheet if changes are confirmed.
c) Imported UniqueIDs that are known and don't have any mismatches remain with white background as no further action is required.

Now what I didn't managed to do so far (yes, here start my request for help

)
1) In the summary table below, I would like to have, in one cell, the count of changed contacts (which should be highlighted in red) regardless of the number of mismatches they have (on their row).
2) In the summary table below, I would like to have, in one cell, the total count of mismatches for all changed contacts (which are highlighted in orange). In other words, if one contact has a new phone number (=1 mismatch) and another contact has a new address and new town value (= 2 mismatches), then I should see in that summary cell the value of: 3

Here are the formulas I use in my conditional format. They could be useful and surely improved.

RED background which applies to: =$A$2:$A$20
UniqueID cell is highlighted in red (not the rest of the row) in the "Imported_Contacts" worksheet when that same UniqueID exists in Known_Contacts worksheet AND at least one mismatch has been detected.
=NOT(AND(Imported_Contacts!A2:J2=INDIRECT("Known_Contacts!A"&MATCH(Imported_Contacts!$A2;Known_Contacts!$A$2:$A$100;0)+1&":J"&MATCH(Imported_Contacts!$A2;Known_Contacts!$A$2:$A$100;0)+1)))

ORANGE background which applies to: =$A$2:$J$20
Details cells are highlighted in orange in the "Imported_Contacts" worksheet whenever there is a mismatch of values for the corresponding UniqueID in the Known_Contacts worksheet.
=A2<>VLOOKUP($A2;Known_Data;COLUMNS($A1:A1);FALSE)

YELLOW background which applies to: =$A$2:$J$20
UniqueID from the Imported_Contacts worksheet not found in the Known_Contacts worksheet are colored (all details of same row) in yellow.
=NOT(ISNUMBER(MATCH($A2;Known_UniqueIDs;0)))

I also use dynamic named ranges, 3 on each Imported and Known worksheets:

_Header (=top row with titles)
_UniqueIDs (all cells with values from column A)
_Data (=full data range A2:Jx)

I've made a sample workbook for you to see, just ping me and I'll send it to you. Here some rows as example:
1° Imported_Contacts

[garbled image code removed]

2° Known_Contacts
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD="width: 98"]UniqueID
[/TD]
[TD="width: 103"]FirstName[/TD]
[TD="width: 100"]LastName[/TD]
[TD="width: 97"]Birthdate[/TD]
[TD="width: 61"]Sex[/TD]
[TD="width: 301"]Country[/TD]
[TD="width: 208"]Town[/TD]
[TD="width: 98"]PostCode[/TD]
[TD="width: 287"]Address[/TD]
[TD="width: 105"]Phone[/TD]
[/TR]
[TR]
[TD]IH12VW[/TD]
[TD]Morgan[/TD]
[TD]Aguilar[/TD]
[TD]01-01-16[/TD]
[TD]M[/TD]
[TD]Sint Maarten[/TD]
[TD]Outrijve[/TD]
[TD]3038[/TD]
[TD]763-6901 A, Impasse[/TD]
[TD]094 656 6238042[/TD]
[/TR]
[TR]
[TD]IP91TX[/TD]
[TD]Illiana[/TD]
[TD]Hancock[/TD]
[TD]26-04-16[/TD]
[TD]M[/TD]
[TD]United States Minor Outlying Islands[/TD]
[TD]Ripacandida[/TD]
[TD]3242[/TD]
[TD]CP 932, 1007 Suspendisse Av.[/TD]
[TD]022 515 7861121[/TD]
[/TR]
[TR]
[TD]GE12HY[/TD]
[TD]Barry[/TD]
[TD]Flores[/TD]
[TD]10-12-14[/TD]
[TD]M[/TD]
[TD]Mozambique[/TD]
[TD]Glovertown[/TD]
[TD]3579[/TD]
[TD]Appartement 889-9536 Ipsum Ave[/TD]
[TD]035 447 6735003[/TD]
[/TR]
[TR]
[TD]ZL32WV[/TD]
[TD]Ima[/TD]
[TD]Burt[/TD]
[TD]29-03-16[/TD]
[TD]M[/TD]
[TD]Montserrat[/TD]
[TD]Lichfield[/TD]
[TD]3757[/TD]
[TD]9460 Sed Avenue[/TD]
[TD]012 053 8279662[/TD]
[/TR]
[TR]
[TD]BC67UC[/TD]
[TD]Oleg[/TD]
[TD]Landry[/TD]
[TD]25-02-15[/TD]
[TD]F[/TD]
[TD]Greece[/TD]
[TD]Fontanigorda[/TD]
[TD]24967[/TD]
[TD]CP 830, 2476 Magna Rd.[/TD]
[TD]016 866 3312095[/TD]
[/TR]
[TR]
[TD]RR69NZ[/TD]
[TD]Rana[/TD]
[TD]Sandoval[/TD]
[TD]04-02-15[/TD]
[TD]M[/TD]
[TD]Slovenia[/TD]
[TD]Val Rezzo[/TD]
[TD]34145[/TD]
[TD]CP 998, 1092 In, Rd.[/TD]
[TD]014 619 6328776[/TD]
[/TR]
[TR]
[TD]HI43ZT[/TD]
[TD]Laith[/TD]
[TD]Mclaughlin[/TD]
[TD]15-11-14[/TD]
[TD]M[/TD]
[TD]Ireland[/TD]
[TD]College[/TD]
[TD]35687[/TD]
[TD]Appartement 677-246 Mauris Av.[/TD]
[TD]099 910 3926914[/TD]
[/TR]
[TR]
[TD]YI95FY[/TD]
[TD]Melinda[/TD]
[TD]Herring[/TD]
[TD]29-10-14[/TD]
[TD]M[/TD]
[TD]Panama[/TD]
[TD]Eksaarde[/TD]
[TD]57127[/TD]
[TD]4806 Augue Impasse[/TD]
[TD]011 022 8788063[/TD]
[/TR]
[TR]
[TD]NA62AQ[/TD]
[TD]Caesar[/TD]
[TD]Pruitt[/TD]
[TD]10-06-16[/TD]
[TD]M[/TD]
[TD]Cocos (Keeling) Islands[/TD]
[TD]Sint-Renelde Saintes[/TD]
[TD]71912[/TD]
[TD]CP 124, 8333 Viverra. Av.[/TD]
[TD]096 528 7849548[/TD]
[/TR]
[TR]
[TD]ST18ED[/TD]
[TD]Jade[/TD]
[TD]Wright[/TD]
[TD]03-12-14[/TD]
[TD]M[/TD]
[TD]American Samoa[/TD]
[TD]Municipal District[/TD]
[TD]910361[/TD]
[TD]Appartement 310-2602 Dolor Rd.[/TD]
[TD]003 584 6316235[/TD]
[/TR]
[TR]
[TD]UZ52JP[/TD]
[TD]Oren[/TD]
[TD]Walter[/TD]
[TD]29-09-15[/TD]
[TD]F[/TD]
[TD]Kuwait[/TD]
[TD]Gateshead[/TD]
[TD]13-709[/TD]
[TD]CP 323, 6841 Diam. Route[/TD]
[TD]013 667 3435189[/TD]
[/TR]
[TR]
[TD]RN15MN[/TD]
[TD]Oleg[/TD]
[TD]Greene[/TD]
[TD]09-08-16[/TD]
[TD]M[/TD]
[TD]Poland[/TD]
[TD]Williams Lake[/TD]
[TD]C2M 7S9[/TD]
[TD]447 Phasellus Chemin[/TD]
[TD]078 095 2592880[/TD]
[/TR]
[TR]
[TD]QY59UE[/TD]
[TD]Aurelia[/TD]
[TD]Stout[/TD]
[TD]10-04-15[/TD]
[TD]M[/TD]
[TD]Ethiopia[/TD]
[TD]Norman[/TD]
[TD]L1J 1C3[/TD]
[TD]2675 Ultrices Chemin[/TD]
[TD]014 821 1574983[/TD]
[/TR]
[TR]
[TD]HV45MH[/TD]
[TD]Zena[/TD]
[TD]Booth[/TD]
[TD]06-09-15[/TD]
[TD]M[/TD]
[TD]Guadeloupe[/TD]
[TD]Northumberland[/TD]
[TD]L8A 0T4[/TD]
[TD]4833 Libero. Rue[/TD]
[TD]004 388 2234326[/TD]
[/TR]
[TR]
[TD]BK93IL[/TD]
[TD]Aurora[/TD]
[TD]Roth[/TD]
[TD]19-05-16[/TD]
[TD]M[/TD]
[TD]Bhutan[/TD]
[TD]Portland[/TD]
[TD]Q2T 2AS[/TD]
[TD]228-3541 Pellentesque Route[/TD]
[TD]086 625 4165753[/TD]
[/TR]
</tbody>[/TABLE]

3° Summary table:
 [TABLE="class: MsoNormalTable, width: 469"]
<tbody>[TR]
[TD="width: 172"]Import date[/TD]
[TD="width: 53"]

17-09-15

[/TD]
[TD="width: 402"][/TD]
[/TR]
[TR]
[TD="width: 172"] Nbr of imported contacts :[/TD]
[TD="width: 53"]

8

[/TD]
[TD="width: 402"][/TD]
[/TR]
[TR]
[TD="width: 172"] Nbr of known contacts :[/TD]
[TD="width: 53"]

16

[/TD]
[TD="width: 402"][/TD]
[/TR]
[TR]
[TD="width: 172"] Nbr of changed Contacts :[/TD]
[TD="width: 53"]

2

[/TD]
[TD="width: 402"] <-- Count of changed contacts (cells in red in the Imported_Contacts sheet) [/TD]
[/TR]
[TR]
[TD="width: 172"] Total nbr of changes :[/TD]
[TD="width: 53"]

3

[/TD]
[TD="width: 402"] <-- Total count of changes (cells in orange in the Imported_Contacts sheet) [/TD]
[/TR]
[TR]
[TD="width: 172"] New contacts imported :[/TD]
[TD="width: 53"]

1

[/TD]
[TD="width: 402"]<[/TD]
[/TR]
[TR]
[TD="width: 172"] Imported known contacts :[/TD]
[TD="width: 53"]

7

[/TD]
[TD="width: 402"]<-- Nbr of imported contacts - new contacts[/TD]
[/TR]
</tbody>[/TABLE]


Wow, that was indeed a long post !!!

Don't hesitate to contact me if you have any suggestion or questions.

Thank you in advance for your help
Pavlos

Solvap · Sep 17, 2015

Hi again,

PFFF post is too long I had to restart it as I had to re-login :-/ Furthermore, I've tried different ways to have my tables with colors but without success :-/
Now there is this whole bunch of crap between these

tags pfff, really sorry for that but I assure you I'm more that frustrated about this!

So, here the table where I coloured the fonts instead of the background as I didn't found any other way to show my tables with colours on this forum.

<tbody>
[TD="class: xl65, align: center"]UniqueID[/TD]
[TD="class: xl65, align: center"]FirstName[/TD]
[TD="class: xl65, width: 100, align: center"]LastName[/TD]
[TD="class: xl65, width: 97, align: center"]Birthdate[/TD]
[TD="class: xl66, width: 95, align: center"]Sex[/TD]
[TD="class: xl65, width: 301, align: center"]Country[/TD]
[TD="class: xl65, width: 208, align: center"]Town[/TD]
[TD="class: xl65, width: 98, align: center"]PostCode[/TD]
[TD="class: xl65, width: 287, align: center"]Address[/TD]
[TD="class: xl65, width: 105, align: center"]Phone[/TD]

[TD="class: xl67"] ST18ED [/TD]
[TD="class: xl67"]Jade[/TD]
[TD="class: xl67"]Wright[/TD]
[TD="class: xl68"]03-12-14[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]American Samoa[/TD]
[TD="class: xl67"] Municipal D. [/TD]
[TD="class: xl67"]910361[/TD]
[TD="class: xl67"]Appartement 310-2602 Dolor Rd.[/TD]
[TD="class: xl67"]003 584 6316235[/TD]

[TD="class: xl67"]HV45MH[/TD]
[TD="class: xl67"]Zena[/TD]
[TD="class: xl67"]Booth[/TD]
[TD="class: xl68"]06-09-15[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]Guadeloupe[/TD]
[TD="class: xl67"]Northumberland[/TD]
[TD="class: xl67"]L8A 0T4[/TD]
[TD="class: xl67"]4833 Libero. Rue[/TD]
[TD="class: xl67"]004 388 2234326[/TD]

[TD="class: xl67"] YI95FY [/TD]
[TD="class: xl67"]Melinda[/TD]
[TD="class: xl67"]Herring[/TD]
[TD="class: xl68"]29-10-14[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]Panama[/TD]
[TD="class: xl67"]Eksaarde[/TD]
[TD="class: xl67"]57127[/TD]
[TD="class: xl67"] 34 Applestreet
[/TD]
[TD="class: xl67"] 011 123 4332483 [/TD]

[TD="class: xl67"]ZL32WV[/TD]
[TD="class: xl67"]Ima[/TD]
[TD="class: xl67"]Burt[/TD]
[TD="class: xl68"]29-03-16[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]Montserrat[/TD]
[TD="class: xl67"]Lichfield[/TD]
[TD="class: xl67"]3757[/TD]
[TD="class: xl67"]9460 Sed Avenue[/TD]
[TD="class: xl67"]012 053 8279662[/TD]

[TD="class: xl67"]QY59UE[/TD]
[TD="class: xl67"]Aurelia[/TD]
[TD="class: xl67"]Stout[/TD]
[TD="class: xl68"]10-04-15[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]Ethiopia[/TD]
[TD="class: xl67"]Norman[/TD]
[TD="class: xl67"]L1J 1C3[/TD]
[TD="class: xl67"]2675 Ultrices Chemin[/TD]
[TD="class: xl67"]014 821 1574983[/TD]

[TD="class: xl67"] TC55PT
[/TD]
[TD="class: xl67"] Audra [/TD]
[TD="class: xl67"] Anthony [/TD]
[TD="class: xl68"] 02-04-15 [/TD]
[TD="class: xl67"] M [/TD]
[TD="class: xl67"] Algeria [/TD]
[TD="class: xl67"] Orosei [/TD]
[TD="class: xl67"] 46-797 [/TD]
[TD="class: xl67"] 967-4353 Hendrerit. Route [/TD]
[TD="class: xl67"] 015 388 9262638 [/TD]

[TD="class: xl67"]BC67UC[/TD]
[TD="class: xl67"]Oleg[/TD]
[TD="class: xl67"]Landry[/TD]
[TD="class: xl68"]25-02-15[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl67"]Greece[/TD]
[TD="class: xl67"]Fontanigorda[/TD]
[TD="class: xl67"]24967[/TD]
[TD="class: xl67"]CP 830, 2476 Magna Rd.[/TD]
[TD="class: xl67"]016 866 3312095[/TD]

[TD="class: xl67"]IP91TX[/TD]
[TD="class: xl67"]Illiana[/TD]
[TD="class: xl67"]Hancock[/TD]
[TD="class: xl68"]26-04-16[/TD]
[TD="class: xl67"]M[/TD]
[TD="class: xl67"]United States Minor Outlying Islands[/TD]
[TD="class: xl67"]Ripacandida[/TD]
[TD="class: xl67"]3242[/TD]
[TD="class: xl67"]CP 932, 1007 Suspendisse Av.[/TD]
[TD="class: xl67"]022 515 7861121[/TD]

</tbody>

I hope it is clear enough, there are:
2 UniqueIDs where at least 1 mismatch has been found
3 Mismatches found in total
1 Unknown UniqueID

No, it's 3:16 am, time for me to go to sleep !!!
Good night all !!!
Pavlos

Solvap · Sep 20, 2015

Hello All,

No idea ?

Let me present it differently, you have a reference table with all your customer details and an imported with some known customers which might need to be updated, some of their details have changed.
So, I need 2 results;
1) How many customers need an update (found with differences based on their ID).
In other words, count how many rows that have the same ID in the reference table but count at least 1 difference with that reference row.

2) Count of the total of differences (cross-customers).
For this result I was thinking of a table of true/false, based on the ID of each found row, any matching value would produce a true and a false if not matching.

Any help is welcomed.

Pavlos

Solvap · Sep 22, 2015

Hello again,

Isn't there someone who could help, plz ? Or at least help me get on the right path ?
I'm thinking of using a sub array of the range of "known_Contacts".
If I could use the unique identifier of the Imported_Contacts" to retrieve all rows having the corresponding unique identifier and then build up 2 arrays, one from each worksheet (known and imported contacts) and both having the same number of rows and columns.

Imported_Contacts_Array

<tbody>
[TD="class: xl65, width: 98"]ST18ED[/TD]
[TD="class: xl63, width: 103"]Jaden
[/TD]
[TD="class: xl64, width: 97"]03-12-14[/TD]
[TD="class: xl66, width: 61"]M[/TD]

[TD="class: xl67"]3EEF32[/TD]
[TD="class: xl67"]Stev
[/TD]
[TD="class: xl68"]23-08-11
[/TD]
[TD="class: xl67"]M[/TD]

[TD="class: xl69"]TC55PT
[/TD]
[TD="class: xl69"]Audra[/TD]
[TD="class: xl70"]02-04-15[/TD]
[TD="class: xl69"]M[/TD]

[TD="class: xl65"]BC67UC
[/TD]
[TD="class: xl66"]Maria
[/TD]
[TD="class: xl64"]25-02-15[/TD]
[TD="class: xl63"]F[/TD]

</tbody>

Known_Contacts_Array

<colgroup><col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> </colgroup><tbody>
[TD="class: xl67, width: 98"]ST18ED[/TD]
[TD="class: xl65, width: 103"]Jade[/TD]
[TD="class: xl66, width: 97"]03-12-14[/TD]
[TD="class: xl68, width: 61"]F[/TD]

[TD="class: xl65"]HV45MH[/TD]
[TD="class: xl65"]Zena[/TD]
[TD="class: xl66"]06-09-15[/TD]
[TD="class: xl65"]M[/TD]

[TD="class: xl65"]YI95FY[/TD]
[TD="class: xl65"]Melinda[/TD]
[TD="class: xl66"]29-10-14[/TD]
[TD="class: xl65"]M[/TD]

[TD="class: xl65"]ZL32WV[/TD]
[TD="class: xl65"]Ima[/TD]
[TD="class: xl66"]29-03-16[/TD]
[TD="class: xl65"]M
[/TD]

[TD="class: xl65"]QY59UE[/TD]
[TD="class: xl65"]Aurelia[/TD]
[TD="class: xl66"]10-04-15[/TD]
[TD="class: xl65"]M[/TD]

[TD="class: xl69"]TC55PT[/TD]
[TD="class: xl69"]Audra[/TD]
[TD="class: xl70"]02-04-15[/TD]
[TD="class: xl69"]M[/TD]

[TD="class: xl67"]BC67UC[/TD]
[TD="class: xl68"]Marina
[/TD]
[TD="class: xl66"]25-02-15[/TD]
[TD="class: xl65"]F[/TD]

</tbody>

Then, there should be then a way to compare each corresponding row, some kind of true/false table, based on:
a) The 1st column (the unique ID) with question; Is known contact identical ?
b) All columns except the 1st one (contact details) with question; Are contact details identical ?

<colgroup><col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> </colgroup><tbody>
[TD="width: 98, align: center"] FALSE
[/TD]
[TD="width: 103, align: center"] FALSE
[/TD]
[TD="width: 97, align: center"] TRUE
[/TD]
[TD="width: 61, align: center"] FALSE
[/TD]

[TD="align: center"] #N/A [/TD]
[TD="align: center"] #N/A [/TD]
[TD="align: center"] #N/A
[/TD]
[TD="align: center"] #N/A
[/TD]

[TD="align: center"] TRUE [/TD]
[TD="align: center"] TRUE
[/TD]
[TD="align: center"] TRUE [/TD]
[TD="align: center"] TRUE [/TD]

[TD="align: center"] FALSE [/TD]
[TD="align: center"] FALSE
[/TD]
[TD="align: center"] TRUE
[/TD]
[TD="align: center"] TRUE
[/TD]

</tbody>

And then count how many contacts might need an update (focusing on 1st column only) as well as the total number of differences (focusing on all column except the 1st one)

Result I would like to achieve is automatically:
Number of contacts where differences have been spotted: 2
Total number of differences spotted within contact details: 3

That's currently the strategy I'm thinking of.

Again, any help would be appreciated.
Pavlos

Compare 2 sheets or ranges (multiple columns) and highlight/count mismatches

Solvap

New Member

Excel Facts

Solvap

New Member

Solvap

New Member

Solvap

New Member

Similar threads

Forum statistics

Share this page

Compare 2 sheets or ranges (multiple columns) and highlight/count mismatches

Solvap

New Member

Excel Facts

Solvap

New Member

Solvap

New Member

Solvap

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock