Duplicate Values

[Shabbo]

Dear Sir,
I wanted to find duplicate values between two lists but if I apply conditional formatting its taking duplicate value even if that is not available in list two.
I wanted to highlight duplicate values only if available in both list.
Please note I can use remove duplicate from list one but data is big and there are so many columns so I can’t apply remove duplicate.

 

[rahulshewale1]

Hii @Shabbo ,

see if is ok ?


Solution using Power Query + CF


Regard
Rahul shewale

 

[Shabbo]

Any other formula because I am using excel 2010 and dont know power Query.

 

[shrivallabha]

You can use a standard construct in cell B2 and copy down till list ends.
=IF(ISNA(MATCH(A2,$A$8:$A$8,0)),"",IF(COUNTIF($A$2:$A$5,A2)>1,"Duplicate",""))

 

[rahulshewale1]

hii @Shabbo


See if is ok ? Using Conditional formating.

CF1.

=IF(ISERROR(VLOOKUP(A2,$E$2:$E$50,1,FALSE)),FALSE,TRUE)

CF2.

IF(ISERROR(VLOOKUP(E2,$A$2:$A$70,1,FALSE)),FALSE,TRUE)



Regard
Rahul shewale

 

[GraH - Guido]

Making a named reference to both tables and the variable list member.

CFs rules:
=ISNUMBER(MATCH(vList;rList2;0)), applied on range of first table
=ISNUMBER(MATCH(vList;rList1;0)), applied on range of second table

 

[Peter Bartholomew]

I used tables and a couple of named formulae (as always)



The conditional formats are then based upon
= duplicated1?
= duplicated2?

The result: