Compare 2 lists - Conditional Formatting

[healey21]

Hello all I have been asked to compare 2 lists to find entries that have either the same code or name or value and highlight both entries.

Example in the list below 3 columns codes name and amount. I have coloured each entry manually which matches. How can I do this with conditional formatting or VBA. There are over 75 thousand rows to check.

[

Excel Workbook
	A	B	C	D	E	F	G
1	List 1				List 2
2	Code	Name	Amount		Code	Name	Amount
3	DE001	H Hll	34		SE001	J Waters	345
4	DE002	M Brown	56		SE002	T Hanks	45.9
5	DE003	Sarah Smith	78		SE003	J Clarskon	34
6	DE004	John Jones	100		DE012	R Hammond	298
7	DE005	J Jones	122		SE005	O Wilde	780.12
8	DE006	K Laws	144		SE006	H Hll	34.23
9	DE007	O Wynn	166		SE007	C Manors	232
10	DE008	S James	188		SE008	S Harvey	123
11	DE009	C Warnes	210		SE009	K Laws	1
12	DE010	L Parry	232		SE010	A Jones	789.9
13	DE011	A Ball	254		SE011	Anne Kellowing	12.45
14	DE012	M Smith	276		DE007	S James	144
15	DE013	D Norman	298		SE013	C Warnes	56
16	DE014	J Allan	320		SE014	D Clarke	386
17	DE015	F Western	342		SE015	T Green	678
18	DE016	B Graham	364		SE016	A Ball	65.98
19	SE015	O Wilde	386		SE017	D Norman	5.9
Sheet1

/QUOTE]

 

[dispelthemyth]

highlight A3:C1000 or the last row you need and enter the below as your conditional formatting formula


=COUNTIF(E$2:E$1000, A3)>0


Highlight E3:G1000 or the last row you need and enter the below as your conditional formatting formula

=COUNTIF(A$2:A$1000, E3)>0



Do you need each of the 3 different comparisons to have different colours, if so instead of highlighting all 3 columns in one go you can do one at a time to select a different colour or edt after doing the above

 

[healey21]

Thank you this works well, I am comparing 1 column against the other.