Conditional Format, If cell matches between two tables

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have two tables that contain mostly matching data. I want to find any cells that dont match each other between the tables.

Is there a way to do this? Conditional Formatting with Index/Match? VBA?

Column A in each table is the UNique Identifier for the Row. , then theres 20 columns of data and 260 rows that need to be checked if they match.

Example:

Unique Id, column 2 , needs to match the same Unique ID, column 2 in the other table.

Hope im explaining this right :)
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,364
Office Version
  1. 2016
Platform
  1. Windows
Hi Hmltnangel,

Does this do what you want?

Hmltnangel2.xlsx
ABCD
1T1T2
212
323
434
555
666
778
889
91011
101112
111214
121415
1315
14
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D9999Expression=AND(D2<>"",ISNA(MATCH(D2,$A$2:$A$9999,0)))textNO
A2:A9999Expression=AND(A2<>"",ISNA(MATCH(A2,$D$2:$D$9999,0)))textNO
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Thanks Toadstool,

Not quite - I think I see where that goes along the lines. Your one basically looks for the Unique ID matching and if the Unique ID is in both tables, then it formats the cell. Is that right?

What I want to do is the next step. If the Unique ID is there - then check if the rest of the data in each of the columns match between the tables. If they dont match - then highlight red.

Work are weird with uploading images or sheets - give me a few mins and I will do a rough idea of it on my personal; laptop instead
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
Table 1
test1.xlsx
ABCDEFGHI
1Unique IDNameCompanyYearRoleSiteRefStatusCost
2John123John DUK3excelwizIOM1234App£145
3John851John FBBC4excelmasterUK1367App£178
4Adam112Adam FUK5excelwizIOM1298App£120
5Eddie109EddieWUK4ExcelnoobIOM1123App£176
PPT


Table 2
test1.xlsx
ABCDEFGHI
1Unique IDNameCompanyYearRoleSiteRefStatusCost
2John123John DUK1excelnoobUK1234App£765
3John851John FUK4excelmasterUK1367App£178
4Adam112Adam FBBC9excelwizIOM1298App£120
5Eddie109EddieWUK4ExcelnoobIOM1123App£176
OldPPT
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
I used a single cell as a test and have it working on that cell now - now to see if the conditional format replicates properly over the tables

=IF(C11=INDEX('Combined Input'!A3:U277,MATCH(A11,'Combined Input'!A3:A277,0),MATCH(C2,'Combined Input'!A2:U2,0)),TRUE,FALSE)
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
204
Office Version
  1. 2016
Platform
  1. Windows
And now the formula from Conditional Formatting that works perfect :D

Ensure that the Applies to Box covers the entire column of data in the table

=$C3<>INDEX('Combined Input'!$A$3:$U$277,MATCH($A3,'Combined Input'!$A$3:$A$277,0),MATCH(C$2,'Combined Input'!$A$2:$U$2,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top