Conditional Format, If cell matches between two tables

[hmltnangel]

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

 

[Toadstool]

Hi Hmltnangel,

Does this do what you want?

Hmltnangel2.xlsx
	A	B	C	D
1	T1			T2
2	1			2
3	2			3
4	3			4
5	5			5
6	6			6
7	7			8
8	8			9
9	10			11
10	11			12
11	12			14
12	14			15
13	15
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D9999	Expression	=AND(D2<>"",ISNA(MATCH(D2,$A$2:$A$9999,0)))	text	NO
A2:A9999	Expression	=AND(A2<>"",ISNA(MATCH(A2,$D$2:$D$9999,0)))	text	NO

 

[hmltnangel]

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]

Table 1

test1.xlsx
	A	B	C	D	E	F	G	H	I
1	Unique ID	Name	Company	Year	Role	Site	Ref	Status	Cost
2	John123	John D	UK	3	excelwiz	IOM	1234	App	£145
3	John851	John F	BBC	4	excelmaster	UK	1367	App	£178
4	Adam112	Adam F	UK	5	excelwiz	IOM	1298	App	£120
5	Eddie109	EddieW	UK	4	Excelnoob	IOM	1123	App	£176
PPT

Table 2

test1.xlsx
	A	B	C	D	E	F	G	H	I
1	Unique ID	Name	Company	Year	Role	Site	Ref	Status	Cost
2	John123	John D	UK	1	excelnoob	UK	1234	App	£765
3	John851	John F	UK	4	excelmaster	UK	1367	App	£178
4	Adam112	Adam F	BBC	9	excelwiz	IOM	1298	App	£120
5	Eddie109	EddieW	UK	4	Excelnoob	IOM	1123	App	£176
OldPPT

 

[hmltnangel]

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]

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))