Search for duplicates won't work with multiple columns

[leigh_ashlin]

Hi, I have the following formula:

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")

This works great if I am looking for duplicates in a single column of data, but it doesn't work if I try it with 2 columns of data.

Does anybody know how I can modify this to search for duplicates from a 'column A' and a 'column B'? (this would be conducted after the duplicates have been removed from each of 'column A' and 'column B' individually)

Thanks - Leigh

 

[pgc01]

Hi Leigh

Please try:

=IF((MAX(COUNTIF(range1,range1)+COUNTIF(range2,range1))>1)+
(MAX(COUNTIF(range1,range2)+COUNTIF(range2,range2))>1),"Duplicates","No Duplicates")

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

Hope this helps
PGC