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

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

3° Summary table:

Import date	17-09-15
Nbr of imported contacts :	8
Nbr of known contacts :	16
Nbr of changed Contacts :	2	<-- Count of changed contacts (cells in red in the Imported_Contacts sheet)
Total nbr of changes :	3	<-- Total count of changes (cells in orange in the Imported_Contacts sheet)
New contacts imported :	1	<
Imported known contacts :	7	<-- Nbr of imported contacts - new contacts

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.

UniqueID	FirstName	LastName	Birthdate	Sex	Country	Town	PostCode	Address	Phone
ST18ED	Jade	Wright	03-12-14	M	American Samoa	Municipal D.	910361	Appartement 310-2602 Dolor Rd.	003 584 6316235
HV45MH	Zena	Booth	06-09-15	M	Guadeloupe	Northumberland	L8A 0T4	4833 Libero. Rue	004 388 2234326
YI95FY	Melinda	Herring	29-10-14	M	Panama	Eksaarde	57127	34 Applestreet	011 123 4332483
ZL32WV	Ima	Burt	29-03-16	M	Montserrat	Lichfield	3757	9460 Sed Avenue	012 053 8279662
QY59UE	Aurelia	Stout	10-04-15	M	Ethiopia	Norman	L1J 1C3	2675 Ultrices Chemin	014 821 1574983
TC55PT	Audra	Anthony	02-04-15	M	Algeria	Orosei	46-797	967-4353 Hendrerit. Route	015 388 9262638
BC67UC	Oleg	Landry	25-02-15	F	Greece	Fontanigorda	24967	CP 830, 2476 Magna Rd.	016 866 3312095
IP91TX	Illiana	Hancock	26-04-16	M	United States Minor Outlying Islands	Ripacandida	3242	CP 932, 1007 Suspendisse Av.	022 515 7861121

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

ST18ED	Jaden	03-12-14	M
3EEF32	Stev	23-08-11	M
TC55PT	Audra	02-04-15	M
BC67UC	Maria	25-02-15	F

Known_Contacts_Array

ST18ED	Jade	03-12-14	F
HV45MH	Zena	06-09-15	M
YI95FY	Melinda	29-10-14	M
ZL32WV	Ima	29-03-16	M
QY59UE	Aurelia	10-04-15	M
TC55PT	Audra	02-04-15	M
BC67UC	Marina	25-02-15	F

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 ?

FALSE	FALSE	TRUE	FALSE
#N/A	#N/A	#N/A	#N/A
TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	TRUE	TRUE

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