Validate unique distinct list in excel
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.
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]Extract unique distinct values from a filtered table [udf and array formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Share this article