How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and also compare columns to columns or rows to rows.
The UNIQUE function is in the Lookup and reference category and is only available to Excel 365 subscribers.
Table of Contents
- UNIQUE Function Syntax
- UNIQUE Function Arguments
- UNIQUE Function example
- Unique distinct values
- Unique values
- Unique distinct rows
- Unique rows
- Columns
- Get Excel file
Links
1. UNIQUE Function Syntax
UNIQUE(array,[by_col],[exactly_once])
2. UNIQUE Function Arguments
Argument | Text |
array | Required. Cell range or array. |
[by_col] | Optional. Boolean value: True or False. True - Compares columns. False (default) - Compares rows. |
[exactly_once] | Optional. Boolean value: True or False. True - Unique rows or columns False - Unique distinct rows or columns. |
3. UNIQUE Function example
Formula in cell D3:
4.0 Unique distinct values
Unique distinct values are all values except that duplicate values are merged into one distinct value.
Formula in cell D3:
The UNIQUE function returns unique distinct values if you only use the first argument with a single-column cell range. The second and third arguments are optional.
Why does the UNIQUE function return a #NAME? error?
First, make sure you spelled the UNIQUE function correctly. If the UNIQUE function still returns a #NAME? error you probably own an earlier Excel version and can't use it unless you upgrade to Excel 365.
There is a small formula you can use if you don't have access to the UNIQUE function, check it out here: How to extract unique distinct values from a column [Formula]
Is the UNIQUE function available for Excel 2003, 2007, 2010, 2013, 2016, and 2019 users?
No, only Excel 365 subscribers have it. However, I made a small formula that works fine, check it out here: How to extract unique distinct values from a column [Formula]
Why does the UNIQUE function return a #SPILL! error?
The UNIQUE function returns an array of values and tries to automatically use the appropriate cell range needed to show all values. If one or more cells are occupied with other values the UNIQUE function returns #SPILL! error.
You have two options, delete or move the values that cause the error or deploy the UNIQUE function in another cell that has adjacent cells below empty.
Why does the UNIQUE function return a #CALC! error?
The UNIQUE function returns a #CALC! error if the output result has no values.
Can I use the UNIQUE function with an Excel Table and structured references?
Yes, you can. The UNIQUE function recalculates the output automatically if you add, edit or delete values in the Excel Table. It works fine with filtered Excel Tables as well.
Is the UNIQUE function case sensitive?
No, it is not case sensitive. Read theses articles if you need a case sensitive formula:
- Extract unique distinct values (case sensitive) [Formula]
- How to extract a case sensitive unique list from a column
4.1 Extract unique distinct values sorted from A to Z
Formula in cell D3:
Check out this article if you own an earlier Excel version and the SORT and UNIQUE functions are not available: Create a unique distinct alphabetically sorted list
Explaining formula in cell D3
Step 1 - Sort values
The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])
The first argument is required, the list is sorted from A to Z if the sort order is omitted.
SORT(B3:B16)
becomes
SORT({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "})
and returns
{"Davydenko, Nikolay ";"Del Potro, Juan Martin ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Djokovic, Novak ";"Djokovic, Novak ";"Federer, Roger ";"Federer, Roger ";"Murray, Andy ";"Murray, Andy ";"Roddick, Andy ";"Roddick, Andy ";"Verdasco, Fernando ";"Verdasco, Fernando "}
Step 2 - Extract unique values
UNIQUE(SORT(B3:B16))
becomes
UNIQUE({"Davydenko, Nikolay ";"Del Potro, Juan Martin ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Djokovic, Novak ";"Djokovic, Novak ";"Federer, Roger ";"Federer, Roger ";"Murray, Andy ";"Murray, Andy ";"Roddick, Andy ";"Roddick, Andy ";"Verdasco, Fernando ";"Verdasco, Fernando "})
and returns
{"Davydenko, Nikolay "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Murray, Andy "; "Roddick, Andy "; "Verdasco, Fernando "}
The UNIQUE function may return an array if more than one value is returned. This will make the formula expand automatically to adjacent cells as far as needed.
How do I return a unique distinct list sorted from Z to A?
Formula in cell D3:
4.2 Extract unique distinct values ignoring blanks
Formula in cell D3:
Check out this article if you own an earlier Excel version and the FILTER and UNIQUE functions are not available: Extract a unique distinct list and ignore blanks
Explaining formula in cell D3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:B16,B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
becomes
FILTER{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})
and returns
{"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}
Step 2 - Extract unique distinct values
UNIQUE(FILTER(B3:B16,B3:B16<>""))
becomes
UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "})
and returns
{"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "}
4.3 Extract unique distinct values sorted from A to Z ignoring blanks
Formula in cell D3:
Check out this article if you own an earlier Excel version and the FILTER and UNIQUE functions are not available:
Create a unique distinct sorted list containing both numbers text removing blanks
Explaining formula in cell D3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:B16,B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
becomes
FILTER{"Federer, Roger "; "Djokovic, Novak "; ""; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; ""; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE})
and returns
{"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}
Step 2 - Extract unique distinct values
UNIQUE(FILTER(B3:B16,B3:B16<>""))
becomes
UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Federer, Roger ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "})
and returns
{"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "}
Step 3 - Sort values
The SORT function has the following arguments: SORT(array,[sort_index],[sort_order],[by_col])
The first argument is required, the list is sorted from A to Z if the sort order is omitted.
SORT(UNIQUE(FILTER(B3:B16,B3:B16<>"")))
becomes
SORT({"Federer, Roger "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Murray, Andy "; "Verdasco, Fernando "})
and returns
{"Davydenko, Nikolay "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Federer, Roger "; "Murray, Andy "; "Roddick, Andy "; "Verdasco, Fernando "}
4.4 Count unique distinct values
Formula in cell F3:
The formula above works fine if your cell range doesn't contain any blank cells, the formula below takes care of blanks.
Formula in cell F3:
The ROWS function counts the number of rows that the UNIQUE function returns, that number is how many distinct values there are in cell range B3:B16.
This article explains a formula that works for earlier Excel versions: Count unique distinct values
5.0 Unique values
Unique values are values that exist only once in a list. The image above shows a list in column B. Item "AA" has a duplicate and is not unique, however, item "BB" exists only once and is unique.
5.1 Extract unique values
Formula in cell D3:
UNIQUE(array, [by_col], [exactly_once])
The third argument takes logical values True or False. True means unique value, in other words, values that only exist once in the list. False means extracting unique distinct values.
There are only two different values that exist once in cell range B3:B15. All other values have duplicates.
Check out this article if you own an earlier Excel version and the UNIQUE function is not available:
How to filter unique values from a list [Formula]
5.2 Extract unique values sorted from A to Z
Formula in cell D3:
Check out this article if you own an earlier Excel version and the UNIQUE function is not available: Filter unique values sorted from A to Z
Explaining formula in cell D3
Step 1 - Extract unique values
The UNIQUE function has the following arguments:
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(B3:B14,,TRUE)
becomes
UNIQUE({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; "Roddick, Andy "},,TRUE)
and returns
{"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando "}
Step 2 - Sort values
The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])
The first argument is required, the list is sorted from A to Z if the sort order is omitted.
SORT(UNIQUE(B3:B14,,TRUE))
becomes
SORT({"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando "})
and returns
{"Davydenko, Nikolay "; "Federer, Roger "; "Verdasco, Fernando "}
5.3 Extract unique values ignoring blanks
The UNIQUE function returns 0 (zero) if there is exactly one blank in the list, however, it disappears if there are two or more blanks in the list.
To remove the blank use the following formula.
Formula in cell D3:
Check out this article if you own an earlier Excel version and the UNIQUE function is not available: How to filter unique values from a list [Formula] It works fine with blanks.
Explaining formula in cell D3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:B16,B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}, {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
and returns
{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}
Step 2 - Extract unique values
The UNIQUE function has the following arguments:
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE)
becomes
UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "},,TRUE)
and returns
{"Federer, Roger ";"Davydenko, Nikolay "}
5.4 Extract unique values sorted from A to Z ignoring blanks
Formula in cell D3:
Check out this article if you own an earlier Excel version and the UNIQUE function is not available: Filter unique values sorted from A to Z It seems to work fine with blanks.
Explaining formula in cell D3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array,include,[if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or critera.
FILTER(B3:B16,B3:B16<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "},{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
becomes
FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}, {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Djokovic, Novak "; ""; "Verdasco, Fernando "; "Murray, Andy "; "Del Potro, Juan Martin "; "Verdasco, Fernando "; "Djokovic, Novak "; "Roddick, Andy "}<>"")
and returns
{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "}
Step 2 - Extract unique values
The UNIQUE function has the following arguments: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE)
becomes
UNIQUE({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Djokovic, Novak ";"Verdasco, Fernando ";"Murray, Andy ";"Del Potro, Juan Martin ";"Verdasco, Fernando ";"Djokovic, Novak ";"Roddick, Andy "},,TRUE)
and returns
{"Federer, Roger ";"Davydenko, Nikolay "}
Step 3 - Sort values
The SORT function has the following arguments:
SORT(array,[sort_index],[sort_order],[by_col])
The first argument is required, the list is sorted from A to Z if the sort order is omitted.
SORT(UNIQUE(FILTER(B3:B16,B3:B16<>""),,TRUE))
becomes
SORT({"Federer, Roger ";"Davydenko, Nikolay "})
and returns
{"Davydenko, Nikolay"; Federer, Roger "}
6.0 Unique distinct rows
Unique distinct rows are all rows except that duplicate rows are merged to one value.
6.1 Extract unique distinct rows
Formula in cell E3:
Check out this article if you own an earlier Excel version and the UNIQUE function returns a #NAME! error:
Filter unique distinct records
The UNIQUE function recognizes automatically a multicolumn cell range and returns unique distinct rows. The image above shows the UNIQUE function returning three rows, row 4 and 6 are merged into one row.
6.2 Extract unique distinct rows ignoring blank rows
Formula in cell E3:
Check out this article if you own an earlier Excel version and the UNIQUE function returns a #NAME! error:
Filter unique distinct row ignore blanks
Explaining formula in cell E3
Step 1 - Filter non-empty rows
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:C6, (C3:C16<>"") * (B3:B6<>"")
becomes
FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, ({1; 2; 2; ""; 2}<>"")*({"AA"; "BB"; "AA"; ""; "BB"}<>""))
becomes
FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, {1;1;1;0;1})
and returns
{"AA", 1; "BB", 2; "AA", 2; "BB", 2}
Step 2 - Extract unique distinct rows
UNIQUE(FILTER(B3:C16,(C3:C16<>"")*(B3:B16<>"")))
becomes
UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2})
and returns
{"AA", 1; "BB", 2; "AA", 2}
6.3 Extract unique distinct rows sorted from A to Z ignoring blank rows
Formula in cell E3:
This formula sorts the array based on the first column and then on the second column.
Explaining formula in cell E3
Step 1 - Filter non-empty rows
The FILTER function is available for Excel 365 subscribers, it has the following arguments: FILTER(array, include, [if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>""))
becomes
FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, ({1; 2; 2; ""; 2}<>"")*({"AA"; "BB"; "AA"; ""; "BB"}<>""))
becomes
FILTER({"AA", 1; "BB", 2; "AA", 2; "", ""; "BB", 2}, {1;1;1;0;1})
and returns
{"AA", 1; "BB", 2; "AA", 2; "BB", 2}
Step 2 - Extract unique distinct rows
UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")))
becomes
UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2})
and returns
{"AA", 1; "AA", 2; "BB", 2}
Step 3 - Sort array
The SORTBY function lets you sort a multicolumn cell range based on the order of columns you define.
SORTBY(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 1), , INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 2), )
becomes
SORTBY({"AA", 1; "BB", 2; "AA", 2}, INDEX({"AA", 1; "BB", 2; "AA", 2}, 0, 1), , INDEX({"AA", 1; "BB", 2; "AA", 2}, 0, 2), )
becomes
SORTBY({"AA", 1; "BB", 2; "AA", 2}, {"AA";"BB";"AA"}, , {1;2;2}, )
and returns
{"AA",1;"AA",2;"BB",2}
Step 4 - Shorten formula
The LET function lets you name an expression that is repeated often in the formula, this allows you to shorten the formula considerably.
SORTBY(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 1), , INDEX(UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), 0, 2), )
x - UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE)
LET(x, UNIQUE(FILTER(B3:C7, (C3:C7<>"")*(B3:B7<>"")), FALSE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))
7.0 Unique rows
Unique rows are rows that exist only once in a cell range. The image above shows that B4:C4 and B6:D6 are duplicates and are not in the result in cell range E3:F4.
7.1 Extract unique rows
Formula in cell E3:
The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False. The formula above is entered as a regular formula.
The following array formula works for older Excel versions than Excel 365.
Array formula in cell E3:
How to enter an array formula
These steps are for earlier Excel versions.
- Copy the above array formula.
- Double-press with left mouse button on cell E3.
- Paste array formula.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter.
- Release all keys.
7.2 Extract unique rows ignoring blank rows
Formula in cell E3:
The formula above is entered as a regular formula.
The following array formula works for older Excel versions than Excel 365.
Array formula in cell E3:
How to enter an array formula
- Copy the above array formula.
- Double-press with left mouse button on cell E3.
- PAste array formula.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter.
- Release all keys.
Explaining Excel 365 formula in cell E3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:C8,(C3:C8<>"")*(B3:B8<>""))
becomes
FILTER({"AA", 1; "BB", 2; "", ""; "AA", 2; "BB", 2; "", ""},({1; 2; ""; 2; 2; ""}<>"")*({"AA"; "BB"; ""; "AA"; "BB"; ""}<>""))
becomes
FILTER({"AA", 1; "BB", 2; "", ""; "AA", 2; "BB", 2; "", ""}, {1;1;0;1;1;0})
and returns
{"AA", 1; "BB", 2; "AA", 2; "BB", 2}
Step 2 - Extract unique rows
The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False meaning distinct values (not unique).
UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), FALSE, TRUE)
becomes
UNIQUE({"AA", 1; "BB", 2; "AA", 2; "BB", 2}, FALSE, TRUE)
and returns
{"AA", 1; "AA", 2}
7.3 Extract unique rows sorted from A to Z ignoring blank rows
This formula sorts the data set based on the first column (B) and the on the second column (C).
Formula in cell E3:
The formula above is entered as a regular formula.
Explaining formula in cell E3
Step 1 - Filter non-empty values
The FILTER function is available for Excel 365 subscribers, it has the following arguments:
FILTER(array, include, [if_empty])
The first argument is the cell range to be filtered, the second argument is the condition or criteria.
FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>""))
becomes
FILTER({"AA", 2; "BB", 2; 0, 0; "AA", 1; "BB", 2; 0, 0}, ({2;2;0;1;2;0}<>"")*({"AA";"BB";0;"AA";"BB";0}<>""))
becomes
FILTER({"AA", 2; "BB", 2; 0, 0; "AA", 1; "BB", 2; 0, 0},{1;1;0;1;1;0})
and returns
{"AA", 2; "BB", 2; "AA", 1; "BB", 2}
Step 2 - Extract unique rows
The third argument in the UNIQUE function determines if rows that exist exactly once should be extracted.
UNIQUE(array,[by_col],[exactly_once]) Default is False meaning distinct values (not unique).
UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE)
becomes
UNIQUE({"AA", 2; "BB", 2; "AA", 1; "BB", 2}, , TRUE)
and returns
{"AA", 2; "AA", 1}
Step 3 - Sort array
The SORTBY function lets you sort a multicolumn cell range based on the order of columns you define.
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
SORTBY(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 1), , INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 2), )
becomes
SORTBY({"AA", 2; "AA", 1}, INDEX({"AA", 2; "AA", 1}, 0, 1), , INDEX({"AA", 2; "AA", 1}, 0, 2), )
becomes
SORTBY({"AA", 2; "AA", 1}, {"AA"; "AA"}, , {2; 1}, )
and returns
{"AA", 1; "AA", 2}
Step 4 - Shrink formula
The LET function lets you name an expression that is repeated often in the formula, this allows you to shorten the formula considerably.
SORTBY(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 1), , INDEX(UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), 0, 2), )
This part: UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE) is repeated three times in the formula.
x - UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE)
LET(x, UNIQUE(FILTER(B3:C8, (C3:C8<>"")*(B3:B8<>"")), , TRUE), SORTBY(x, INDEX(x, 0, 1), , INDEX(x, 0, 2), ))
8.0 Unique and unique distinct columns
8.1 Extract unique distinct columns
Formula in cell E3:
The formula above is entered as a regular formula.
8.2 Extract unique columns
Formula in cell E3:
The formula above is entered as a regular formula.
9.0 Get Excel file
'UNIQUE' function examples
The following 35 articles contain the UNIQUE function.
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
What's on this page Unique distinct values sorted based on frequency Unique distinct values sorted based on frequency - Excel […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Functions in this article
Functions in 'Lookup and reference' category
The UNIQUE function function is one of many functions in the 'Lookup and reference' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form