» Checking For Duplicate Values Within A Range

CATEGORY - Excel Formula and Function VERSION - All Microsoft Excel Versions

Problem:

Columns A & B contain two lists of values.
We want to create a formula that will check whether there is any duplication of values within either list (blank cells are to be ignored).

Solution:

Use the COUNTA, SUMPRODUCT, and COUNTIF functions as shown in the following formula:
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7<>"")/COUNTIF(A2:A7,A2:A7&"")),"No Duplicates","Duplicates")
The formula will return "Duplicates" if the list contains duplicate values, otherwise it will return "No Duplicates".

List1____List2
1________1
2
3________B
A________2
________3
1________4
Screenshot // Checking For Duplicate Values Within A Range