validate duplicates

Validate "All duplicate values" list (D2:D7)

Array formula in E13:

=AND(COUNTIF(list, all_duplicates)=COUNTIF(all_duplicates, all_duplicates)) + CTRL + SHIFT + ENTER

Validate "Values having duplicates" list (F3:F4)

Array formula in E17:

=AND(AND(COUNTIF(list, Duplicates)>1), AND(COUNTIF(Duplicates, Duplicates)=1)) + CTRL + SHIFT + ENTER

Named ranges

all_duplicates (D3:D7)
Duplicates (F3:F4)
list (B3:B9)
What is named ranges?

How to implement array formula to your workbook

Change the named ranges.

Download excel file for this tutorial.

Validate duplicates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

AND(logical1, logical2)
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE