Unique distinct values from multiple columns using array formula
Question: I have cell values spanning over several columns and I want to create a unique list from that range. How?
Answer:
Thanks to Eero, who contributed the original array formula!
Unique distinct text values from range tbl_text, array formula in B13:
Copy cell B13 and paste it down as far as necessary.
Unique distinct num values from range tbl_num, array formula in D13:
Copy cell D13 and paste it down as far as necessary.
Explaining array formula in cell B13
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
The array formula has two parts. One part returns row numbers and the other part returns column numbers. Let us begin with the first part, returning row numbers.
Step 1 - Find new unique distinct text values
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
COUNTIF(range,criteria) - Counts the number of cells within a range that meet the given condition
COUNTIF($B$12:B12, tbl_text)=0
becomes
COUNTIF("Text", {"Apple","Banana","Lemon";"Orange","Lemon","Apple";"Lemon","Banana","Orange"})=0
becomes
{0,0,0;0,0,0;0,0,0}=0
becomes
{TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}
Step 2 - Convert boolean array to row numbers
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
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
IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)
becomes
IF({TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}, {2;3;4}-MIN({2;3;4})+1)
becomes
IF({TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}, {2;3;4}-MIN({2;3;4})+1)
becomes
IF({TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}, {2;3;4}-2+1)
becomes
IF({TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}, {1;2;3}) and returns {1,1,1;2,2,2;3,3,3}
Step 3 - Extract smallest value in array
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1))
becomes
MIN({1,1,1;2,2,2;3,3,3}) and returns 1.
Step 4 - Part two, identify array values in current row
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
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
INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1))
becomes
INDEX(tbl_text, MIN({1,1,1;2,2,2;3,3,3}), , 1))
becomes
INDEX(tbl_text, 1, , 1)) returns array {"Apple", "Banana", "Lemon"}
Step 5 - Find new unique distinct text values in current row
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition
COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1))
becomes
COUNTIF("Text", {"Apple", "Banana", "Lemon"}) and returns {0,0,0}
Step 6 - Find a new unique distinct text value in current row
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value
MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0)
becomes
MATCH(0, {0,0,0}, 0) returns 1.
Step 7 - All together
=INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)
becomes
=INDEX(tbl_text, 1, 1) returns value "Apple" in cell B13.
Explaining array formula in cell D13
=LARGE(IF(COUNTIF($D$12:D12, tbl_num)=0, tbl_num, ""), 1)
Step 1 - Remove previously extracted values above current cell with an array with boolean values
=LARGE(IF(COUNTIF($D$12:D12, tbl_num)=0, tbl_num, ""), 1)
COUNTIF(range,criteria) - Counts the number of cells within a range that meet the given condition
COUNTIF($D$12:D12, tbl_num)=0
becomes
{0,0,0;0,0,0;0,0,0}=0
becomes
{TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}
Step 2 - Convert boolean values to numeric values
=LARGE(IF(COUNTIF($D$12:D12, tbl_num)=0, tbl_num, ""), 1)
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
IF(COUNTIF($D$12:D12, tbl_num)=0, tbl_num, "")
becomes
IF({TRUE,TRUE,TRUE;TRUE,TRUE,TRUE;TRUE,TRUE,TRUE}, {1, 2, 1;2, 4, 3;1, 3, 1}, "")
becomes
{1, 2, 1;2, 4, 3;1, 3, 1}
Step 3 - Convert boolean values to numeric values
LARGE(array,k) returns the k-th largest row number in this data set.
=LARGE(IF(COUNTIF($D$12:D12, tbl_num)=0, tbl_num, ""), 1)
becomes
=LARGE({1, 2, 1;2, 4, 3;1, 3, 1}, 1) returns 4 in cell D13.
Download excel sample file for this article.
Unique-distinct-values-from-multiple-columns-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article
ROW(reference) Returns the rownumber of a reference
ROWS(array) returns the number of rows in a reference or an array
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
LARGE(array,k) returns the k-th largest row number in this data set.
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
This blog article is one out of thirteen articles on the same subject "unique".
- How to extract a unique distinct list from a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct list from a date range in excel
- Unique values from multiple columns using array formulas
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Filter unique distinct values from two ranges combined in excel 2007
- Create a unique list and sort by occurrances from large to small
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
External resources:
Identifying Unique Values In An Array Or Range (VBA function)
Related posts:
Extract unique values from a range using array formula in excel
Comparing two columns and sum unique values using array formula in excel
Extract a unique distinct list from two columns using excel 2007 array formula
Extract largest values from two columns using array formula in excel
Recommended categories
A slightly different approach to extract unique items from a N*M table (named as "tbl" in the formula).
So type say "Unique items from the table" in A1 and enter the following formula as an array into A2 and copy it down as far as necessary.(it is supposed column A to be free)
=INDEX(tbl,MIN(IF(COUNTIF($A$1:A1,tbl)=0,ROW(tbl)-MIN(ROW(tbl))
+1)),MATCH(0,COUNTIF($A$1:A1,INDEX(tbl,MIN(IF(COUNTIF($A$1:A1,tbl)=0,ROW(tbl)-MIN
(ROW(tbl))+1)),,1)),0),1)
Thank you! Your formula is working perfectly! No need for a "helper" column!
Hi,
How I can extend "tbl_text" as reported in your example??
I need to enlarge that range for a bigger table.
Thanks,
Fabio,
Use "Name Manager" to change range.
http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA010147120.aspx
It works now!
Many thanks,
Fabio
If a blank cell is located anywhere in the tbl, the formula returns the blank. I guess technically a blank is a unique value in the tbl but I'm trying to make sure only relevant numbers are returned. Any thoughts on how to correct this?
Curious,
Download example file:
Unique-distinct-values-from-multiple-columns-using-array-formulas-without-blanks.xls
Hi Oscar,
at the end there is a #N/A in this file can you please suggest me how to get rid of it.
thanks for your help.
Sandeep,
=IFERROR(formula, "")
I have tried the formulas in this article and some from other articles and comments, but none have worked for my particular problem. I'd appreciate any help/insight.
I have several worksheets, each with a table inserted. I would like to create the list of uniques in the column of the summary worksheet's table. The methods on this site work for creating a list from a 1/2/3 columns, but fails for multiple columns (in my case). I have 4 and I'd rather understand the "general" approach than keep creating ever more convoluted formulas as columns increase.
I have created a named range that spans 4 worksheets (in the Name Manager - Name: MultiPC Refers To=A[PC],B[PC],C[PC],D[PC] -- references 4 table columns on separate worksheets).
The formulas create several errors. Stepping through them, when it tries to evaluate INDEX(*MultiPC*,... it says that it will result in an error. The value for MultiPC shown below the formula is the absolute references for MultiPC (comma separated between sheets, e.g. Sheet1!$B$2:$B$31,Sheet2!$B$2:$B:23...).
I'm guessing it's because the named range doesn't consitute an array (not rectangular? is this the case with all non-contiguous ranges?). I'm not really sure if that's the problem and how to tackle it. I've thought about making hidden columns in a single worksheet for the unique list of each worksheet, then applying this approach. Another alternative might be to extend the 3-column method from here http://www.get-digital-help.com/2009/06/20/extract-a-unique-distinct-list-from-three-columns-in-excel/ (add another nested IFERROR(INDEX...MATCH(...COUNTIF(... ), but again, I'm trying to learn a general solution that doesn't require an ever-expanding formula.
Of course, it'd be a cinch if I was allowed to use VBA for this project, but our workplace doesn't allow macros, so I'm stuck using formulas at the moment. What's your opinion? Thanks a lot!
Zeta,
I have several worksheets, each with a table inserted. I would like to create the list of uniques in the column of the summary worksheet's table. The methods on this site work for creating a list from a 1/2/3 columns, but fails for multiple columns (in my case). I have 4 and I'd rather understand the "general" approach than keep creating ever more convoluted formulas as columns increase.
Here is an example of four columns:
how-to-extract-a-unique-list-from-four-columns-in-excel.xlsx
I'm guessing it's because the named range doesn't consitute an array (not rectangular? is this the case with all non-contiguous ranges?). I'm not really sure if that's the problem and how to tackle it. I've thought about making hidden columns in a single worksheet for the unique list of each worksheet, then applying this approach. Another alternative might be to extend the 3-column method from here http://www.get-digital-help.com/2009/06/20/extract-a-unique-distinct-list-from-three-columns-in-excel/ (add another nested IFERROR(INDEX...MATCH(...COUNTIF(... ), but again, I'm trying to learn a general solution that doesn't require an ever-expanding formula.
I am sorry, I don´t have a general solution to this problem.
Oscar,
I really appreciate your reply. Your site is an incredible resource. I used the 4-column formula you provided, but if I find a method that works for N columns across multiple sheets, I will let the folks here know!
Thanks again,
Z
Thank you for writing this, it works like a charm!
However, there is one thing I would like to do different:
When I enter more entries in the array, the list updates with new entries in the order of first looking through the row, then going down the column. I would prefer the list first list the unique values in the column going downward, then then next column downward etc. Is that possible?
Thank you
Jonas,
See this file:
Unique-distinct-values-from-multiple-columns-using-array-formulas-jonas.xls
[...] The answer is that there is no need for multiple duplicate columns in the array. Excel simplifies the array down to a single column. But when used with multiple cell ranges in more complicated array formulas, make sure the number of rows match. See this example: Unique distinct values from a cell range [...]