Problem:

Columns A:B contain two lists of values.
How could we create a formula that will check whether each of the lists contains duplicate values (disregarding blanks)?

Solution:

Using the COUNTA and SUMPRODUCT functions as follows:
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7
The formula will return \"\"Duplicates\"\" in case the list includes duplicate values and \"\"No Duplicates\"\" in case such values are not included.


List1____List2
1________1
2
3________B
A________2
________3
1________4