If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.

The list below includes a duplicated number 45 in cells A3 and A7. The formula in cell C1 returns 45   using the MODE function. See image below.

Formula in cell C1.

=MODE(A2:A8)

The MODE function returns the entry that appears the most frequently in a list. That can be a value, date or text.

Note MODE ignores blank cells or cells with formulas that return a blank cell.

The interesting thing about the MODE function is it returns the #N/A error if all the entries in the list are unique or no duplicates. See image below where I have changed cell A7 from 45 to 46.

We can use that error to our advantage with the ISNA function.

The ISNA function returns TRUE if a formula returns the #N/A error and FALSE if it doesn’t. See image below for the amended formula in cell C1.

Amend formula for C1

=ISNA(MODE(A2:A8))

If I change cell A7 back to 45 you can see the result in the image below.

Cell C1 now returns TRUE if the list is unique and FALSE if it isn’t.

Note since MODE ignores blank cells there can be multiple blank cells in the range and this formula will still show TRUE.