I created three columns. The first column contains "Error values", values that are not unique distinct values. Missing values are values that are unique distinct but missing in the unique distinct list. Duplicate values are values that are unique distinct values in the original list but exists more than once in the unique distinct list.

Is "Unique distinct list" valid?

Here is an array formula in F22 that returns TRUE or FALSE if "unique distinct list" is valid:

Error values

Array formula in C15:

copied down as far as needed.

Missing values

Array formula in E15:

copied down as far as needed.

Duplicate values

Array formula in G15:

copied down as far as needed.

Named ranges

unique_list (E3:E9)
list (C3:C9)
What is named ranges?

How to implement array formula to your workbook

Change the named range. If your list starts at, for example, F3. Change $E$14:E14 (or $G$14:G14) to F2:$F$2, in the above formulas.

Download excel file for this tutorial.

Validate unique distinct list.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

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.

Share this article

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

Recommended posts

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

How to create a unique distinct list where other columns meet two criteria

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

Unique distinct list sorted based on occurrance in a column in excel

Question: How do I create a unique distinct list from a column, sorted by occurance? Answer: Array formula in A17: […]

Extract unique distinct text list from a range containing both numerical and text values

Formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14,tbl)=0)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+COUNTIF($B$14:B14,INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*ISTEXT(tbl),ROW(tbl)-MIN(ROW(tbl))+1)), , 1)), 0), 1) + CTRL […]

Validate unique list in excel

Question: My list contains text values and I have filtered a new list with unique text values. I would like […]

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]