*The tutorial looks at how to get unique values in Excel by using the UNIQUE function and dynamic arrays. You will learn a simple formula to find unique values in a column or row, in multiple columns, based on conditions, and a lot more.*

In the previous versions of Excel, extracting a list of unique values was a hard challenge. We have a special article that shows how to find uniques that occur just once, extract all distinct items in a list, ignore blanks, and more. Each task required a combined use of several functions and a multi-line array formula that only Excel gurus can fully understand.

The introduction of the UNIQUE function in Excel 365 has changed everything! What used to be a rocket science becomes as easy as ABC. Now, you don't need to be a formula expert to get unique values from a range, based on one or multiple criteria, and arrange the results in alphabetical order. All is done with simple formulas that everyone can read and adjust for your own needs.

The UNIQUE function in Excel returns a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc.

The function is categorized under Dynamic Arrays functions. The result is a dynamic array that automatically spills into the neighboring cells vertically or horizontally.

The syntax of the Excel UNIQUE function is as follows:

UNIQUE(array, [by_col], [exactly_once])

Where:

**Array** (required) - the range or array from which to return unique values.

**By_col** (optional) - a logical value indicating how to compare data:

- TRUE - compares data across columns.
- FALSE or omitted (default) - compares data across rows.

**Exactly_once** (optional) - a logical value that defines what values are considered unique:

- TRUE - returns values that occur only once, which is the database notion of unique.
- FALSE or omitted (default) - returns all distinct (different) values in the range or array.

Below is an Excel unique values formula in its simplest form.

The goal is to extract a list of unique names from the range B2:B10. For this, we enter the following formula in D2:

`=UNIQUE(B2:B10)`

Please notice that the 2nd and 3rd arguments are omitted because the defaults work perfectly in our case - we are comparing the rows against each other and wish to return all the different names in the range.

When you press the Enter key to complete the formula, Excel will output the first found name in D2 spilling the other names into the cells below. As the result, you have all the unique values in a column:

In case your data is across the columns from B2 to I2, set the 2^{nd} argument to TRUE to compare the columns against each other:

`=UNIQUE(B2:I2,TRUE)`

Type the above formula in B4, press Enter, and the results will spill horizontally into the cells to the right. Thus, you'll get the unique values in a row:

UNIQUE is a new function in Excel 365 and like other dynamic array functions has a few specificities that you should be aware of:

- If the array returned by UNIQUE is the final result (i.e. not passed to another function), Excel dynamically creates an appropriately sized range and populates it with the results. The formula needs to be entered only in
**one cell**. It is important that you have enough empty cells down and/or to the right of the cell where you enter the formula, otherwise a #SPILL error occurs. - The results
**update automatically**when the source data changes. However, new entries that are added outside of the referenced array are not included in the formula unless you change the*array*reference. If you want the*array*to respond to the resizing of the source range automatically, then convert the range to an Excel table and use structured references, or create a dynamic named range. - Dynamic arrays between different Excel files only work when
**both workbooks are open**. If the source workbook is closed, a linked UNIQUE formula will return a #REF! error. - Like other dynamic array functions, UNIQUE can only be used within a normal
**range**, not a table. When put within Excel tables, it returns a #SPILL! error.

The below examples show some practical uses of the UNIQUE function in Excel. The main idea is to extract unique values or remove duplicates, depending on your viewpoint, in the simplest possible way.

To get a list of values that appear in the specified range exactly once, set the 3^{rd} argument of UNIQUE to TRUE.

For example, to pull the names that are on the winners list one time, use this formula:

`=UNIQUE(B2:B10,,TRUE)`

Where B2:B10 is the source range and the 2^{nd} argument (*by_col*) is FALSE or omitted because our data is organized in rows.

If you are pursuing an opposite goal, i.e. are looking to get a list of values that appear in a given range more than one time, then use the UNIQUE function together with FILTER and COUNTIF:

UNIQUE(FILTER(*range*, COUNTIF(*range*, *range*)>1))

For example, to extract different names that occur in B2:B10 more than once, you can use this formula:

`=UNIQUE(FILTER(B2:B10, COUNTIF(B2:B10, B2:B10)>1))`

**How this formula works:**

At the heart of the formula, the FILTER function filters out duplicate entries based on the count of occurrences, returned by the COUNTIF function. In our case, the result of COUNTIF is this array of counts:

`{4;1;3;4;4;1;3;4;3}`

The comparison operation (>1) changes the above array to TRUE and FALSE values, where TRUE represents the items that appear more than once:

`{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}`

This array is handed off to FILTER as the *include* argument, telling the function which values to include in the resulting array:

`{"Andrew";"David";"Andrew";"Andrew";"David";"Andrew";"David"}`

As you can notice, only the values corresponding to TRUE survive.

The above array goes to the *array* argument of UNIQUE, and after removing duplicates it outputs the final result:

`{"Andrew";"David"}`

In situation when you want to compare two or more columns and return the unique values between them, include all the target columns in the *array* argument.

For instance, to return the unique First name (column A) and Last name (column B) of the winners, we enter this formula in E2:

`=UNIQUE(A2:B10)`

Pressing the Enter key yields the following results:

To get **unique rows**, i.e. the entries with the unique combination of values in columns A, B and C, this is the formula to use:

`=UNIQUE(A2:C10)`

Amazingly simple, isn't it? :)

How do you usually alphabetize in Excel? Right, by using the inbuilt Sort or Filter feature. The problem is you need to re-sort every time your source data changes, because unlike Excel formulas that recalculate automatically with every change in the worksheet, the features have to be re-applied manually.

With the introduction of dynamic array functions this problem is gone! What you need to do is simply warp the SORT function around a regular UNIQUE formula, like this:

SORT(UNIQUE(array))

For example, to extract unique values in columns A through C and arrange the results from A to Z, use this formula:

`=SORT(UNIQUE(A2:C10))`

Compared to the above example, the output is a lot easier to perceive and work with. For instance, we can clearly see that Andrew and David have been winners in two different sports.

When searching in multiple columns, by default, the Excel UNIQUE function outputs each value in a separate cell. Perhaps, you'll find it more convenient to have the results in a single cell?

To achieve this, instead of referencing the entire range, use the ampersand (&) to concatenate the columns and put the desired delimiter in between.

As an example, we are concatenating the first names in A2:A10 and the last names in B2:B10, separating the values with a space character (" "):

`=UNIQUE(A2:A10&" "&B2:B10)`

As the result, we have a list of full names in one column:

To extract unique values with condition, use the Excel UNIQUE and FILTER functions together:

- The FILTER function limits the data only to values that meet the condition.
- The UNIQUE function removes duplicates from the filtered list.

Here's the generic version of the filtered unique values formula:

UNIQUE(FILTER(array, *criteria_range* = *criteria*))

For this example, let's get a list of winners in a specific sport. For starters, we input the sport of interest in some cell, say F1. And then, use the below formula to get the unique names:

`=UNIQUE(FILTER(A2:B10, C2:C10=F1))`

Where A2:B10 is a range to search for unique values and C2:C10 is the range to check for the criteria.

To filter unique values with two or more conditions, use the expressions like shown below to construct the required criteria for the FILTER function:

UNIQUE(FILTER(array, (*criteria_range1* = *criteria1*) * (*criteria_range2* = *criteria2*)))

The result of the formula is a list of unique entries for which all of the specified conditions are TRUE. In terms of Excel, this is called the AND logic.

To see the formula in action, let's get a list of unique winners for the sport in G1 (criteria 1) and under the age in G2 (criteria 2).

With the source range in A2:B10, sports in C2:C10 (criteria_range 1) and ages in D2:D10 (criteria_range 2), the formula takes this form:

`=UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<G2)))`

And returns exactly the results we are looking for:

**How this formula works:**

Here's a high-level explanation of the formula's logic:

In the *include* argument of the FILTER function, you supply two or more range/criteria pairs. The result of each logical expression is an array of TRUE and FALSE values. The multiplication of the arrays coerces the logical values to numbers and produces an array of 1's and 0's. Since multiplying by zero always gives zero, only the entries that meet all the conditions have 1 in the final array. The FILTER function filters out the items corresponding to 0 and hands off the results to UNIQUE.

For more information, please see FILTER with multiple criteria using AND logic.

To get a list of unique values based on multiple OR criteria, i.e. when this OR that criterion is TRUE, add the logical expressions instead of multiplying them:

UNIQUE(FILTER(array, (*criteria_range1* = *criteria1*) + (*criteria_range2* = *criteria2*)))

For example, to show the winners in either *Soccer* or *Hockey*, you can use this formula:

`=UNIQUE(FILTER(A2:B10, (C2:C10="Soccer") + (C2:C10="Hockey")))`

If needed, you can of course enter the criteria in separate cells and refer to those cells like shown below:

`=UNIQUE(FILTER(A2:B10, (C2:C10=G1) + (C2:C10=G2)))`

**How this formula works:**

Just like when testing multiple AND criteria, you place several logical expressions in the *include* argument of the FILTER function, each of which returns an array of TRUE and FALSE values. When these arrays are added up, the items for which one or more criteria is TRUE will have 1, and the items for which all the criteria are FALSE will have 0. As the result, any entry that meets any single condition makes it into the array that is handed over to UNIQUE.

For more information, please see FILTER with multiple criteria using OR logic.

If you are working with a data set that contains some gaps, a list of uniques obtained with a regular formula is likely to have an empty cell and/or zero value. This happens because the Excel UNIQUE function is designed to return all distinct values in a range, including blanks. So, if your source range has both zeros and blank cells, the unique list will contain 2 zeros, one representing a blank cell and the other - a zero value itself. Additionally, if the source data contains empty strings returned by some formula, the uique list will also include an empty string ("") that visually looks like a blank cell:

To get a list of unique values without blanks, this is what you need to do:

- Filter out blank cells and empty strings by using the FILTER function.
- Utilize the UNIQUE function to limit results to unique values only.

In a generic form, the formula looks as follows:

UNIQUE(FILTER(*range*, *range*<>""))

In this example, the formula in D2 is:

`=UNIQUE(FILTER(B2:B12, B2:B12<>""))`

As the result, Excel returns a list of unique names without empty cells:

Sometimes you may want to extract unique values from two or more columns that are not adjacent to each other. At times, you may also want to re-order the columns in the resulting list. Both tasks can be fulfilled with help of CHOOSE function.

UNIQUE(CHOOSE({1,2,…}, *range1*, *range2*))

From our sample table, suppose you wish to get a list of winners based on the values in columns A and C and arrange the results in this order: first a sport (column C), and then a sportsman name (column A). To have it done, we construct this formula:

`=UNIQUE(CHOOSE({1,2}, C2:C10, A2:A10))`

And get the following result:

**How this formula works:**

The CHOOSE function returns a 2-dimentional array of values from the specified columns. In our case, it also swaps the order of columns.

{"Basketball","Andrew"; "Basketball","Betty"; "Volleyball","David"; "Basketball","Andrew"; "Hockey","Andrew"; "Soccer","Robert"; "Volleyball","David"; "Hockey","Andrew"; "Basketball","David"}

From the above array, the UNIQUE function returns a list of unique records.

The UNIQUE formulas we've discussed in this tutorial work just perfect… provided there is at least one value that meets the specified criteria. If the formula does not find anything, a #CALC! error occurs:

To prevent this from happening, simply wrap your formula in the IFERROR function.

For example, if no unique values meeting the criteria are found, you can display nothing, i.e. an empty string (""):

`=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<G2))), "")`

Or you can clearly inform your users that no results are found:

`=IFERROR(UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<G2))), "No results")`

As you have seen, the emergence of the UNIQUE function has made finding unique values in Excel incredibly easy. If all of a sudden your formula results in an error, it's most likely to be one of the following.

Occurs if you use a UNIQUE formula in an Excel version where this function is not supported.

Currently, the UNIQUE function is only available with Office 365 subscriptions, in the Monthly channel. If you have a different version, you may find an appropriate solution in this tutorial: How to get unique values in Excel 2019, Excel 2016 and earlier.

The #NAME? error in Excel 365 signifies that the function's name is misspelled.

Occurs if one or more cells in the spill range are not completely blank.

To fix the error, just clear or delete non-empty cells. To see exactly which cells are getting in the way, click the error indicator, and then click *Select Obstructing Cells*. For more information, please see #SPILL! error in Excel - causes and fixes.

That's how to find unique values in Excel. I thank you for reading and hope to see you on our blog next week!

Excel unique values formula examples (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 68 responses to "UNIQUE function - quick way to find unique values in Excel"

Any method to use UNIQUE over non-contiguous tables?

=UNIQUE((A1:A9,A11:A14,E1:E9),FALSE,FALSE)

Obviously that doesn't work, but that what i'm aiming to do.

Hello!

Unfortunately the array must be a rectangular and contiguous range of cells

Is it possible to use =UNIQUE(FILTER(B2:B12, B2:B12"")) over multiple columns to remove blanks? For example =UNIQUE(FILTER(B2:D12, B2:D12""))which currently returns #VALUE!

Hi,

I have 3 different date columns

Re Qua Bu

7/17/2020 8/17/2020 9/10/2020

7/18/2020 8/20/2020 9/17/2020

7/19/2020 8/21/2020 10/6/2020

I want to get unique detail for this 3 columns in another cell as it has to be linked to the date range..

Is it possible?

Hello!

Here is the article that may be helpful to you: How to get a list of unique values in Excel

I want to use the results of the Unique function as part of a table, i.e. I want to add columns to the results of the Unique function in order to perform other calculation. Putting that in a table would allow me to define the formulas on the first row only and the spill of the Unique function would define the number of rows of my table.

I have created the first two columns of the table using Unique (i.e. to find unique pairs in another table) and started to add more headers before creating the final table. As soon as the table is created, I am getting error in the columns containing the spill of the Unique function. Is there a way to go around this problem?

Hello!

Sorry, I do not fully understand the task. For me to be able to help you better, please specify which formula you mean and describe the problem in more detail.

Hi i need below mentioned details formula. Kindly help on this.

A columns value Greater than 0 and need to count B columns unique count only.

This is very helpful - thank you. I have a question though. I want to filter unique values based on multiple criteria but rather than extracting the results that meet both criteria I want to extract the results that meet just one of the two criteria given. E.g. in the examples above, lets say the two criteria were Basketball OR Volleyball then the result would be the 6 cases where either of these two conditions are met. Any ideas? Thanks

Hello!

To use 2 conditions with OR logic in the selection of unique values, use the formula

=UNIQUE(FILTER(A2:B10,(C2:C10=F1)+(C2:C10=G1)))

where G1 -- "Volleyball"

I hope my advice will help you solve your task.

Brilliant! That works perfectly. Thank you so much for the quick reply as well.

Can I create a unique formula that produces only the unique values that contain a specific string of text which I define?

Hello!

To find unique values by a string of text, you can use the formula

=UNIQUE(FILTER(A2:B10,ISNUMBER(SEARCH("Bas",C2:C10,1))))

I hope it’ll be helpful.

Hi - great tips, thanks.

How can you use the UNIQUE function on a table where you keep adding to the columns but dont want to keep updating the formula?

for example if you had =SUM(1/COUNTIF(F2:F34,F2:F34)) but wanted to keep adding values to the F column without having to change the formula everytime?

Thanks

Hello!

You can use dynamic named range or Excel table. I hope my advice will help you solve your task.

How do I find unique fields based on 3-4 different criteria? I'm trying to avoid creating multiple pivot tables. I've been researching this and i'm not sure if it is possible.

Hello!

If I understand your task correctly, pay attention to the following paragraph of the article above: Filter unique values based on multiple criteria

I hope my advice will help you solve your task.

I'm able to filter if I add 1 additional criteria and it works. If I add another criteria to the equation it errors out and produces calc. This is where the confusion on my part comes from.

=UNIQUE(FILTER(SFDC!B:B,(SFDC!G:G=Sheet1!B3)*(SFDC!M:M=92)*(SFDC!M:M=91)*(SFDC!M:M=21)))

Hello!

I don't have your data. Therefore, I cannot determine the cause of the error. Note, however, that the!CALC error occurs when the UNIQUE + FILTER formula finds nothing. Read more in this section above.

I hope it’ll be helpful.

How can use unique function in excel 2013

Hello!

The UNIQUE function does not work in Excel 2013. It is only available in Excel 365.

I have used the UNIQUE function and get the same word twice, in this instance the word "Pollard" appears twice. I have tried copying from one cell into all of the cells with this word yet it keeps seeing the same word as two unique entries. Any ideas? Thanks.

Hi,

How can I count the number of items returned by UNIQUE(FILTER when filter returns no results ? In this case, ROWS or COUNTA return 1, when I would like to have 0

Thnaks

Olivier

Bookmarking this - thanks so much! I've been looking for resources on how to do this for ages - I manage a lot of payment data updated weekly and frequently need to find unique donor IDs within a certain date range.

Quick question: I'm trying to find a count for only unique IDs that show up for the first time within a given date range. So for example, pulling a list of all unique IDs for 1/1/20-12/7/20, then pulling a list of unique IDs for 12/7/20-12/14/20, and counting/keeping only the IDs that are in the second range and NOT the first. Do you know of any way to do this?

Thanks!

Hi, thanks for these articles. I'm hoping you can help me with an application of these functions in a different scenario.

I've got a large data set of people located in different facilities. The names are in one column and the facilities are in another. In a third column is a date on which occurred a specific event. Because these events are recurring, each name and facility may show up many times.

I used =Unique(Filter( to pull all the unique names from each facility, and it worked great. I was hoping to use a Countif function to see how many times each name is in the list, but I cannot reference the cells with the names that the unique(filter returned. Is there a good solution to count how many times each unique name shows up in the list without having to type the name out in separate Countif functions?

Example Data Set UNIQUE(FILTER(A:A,B:B="Facility A")) # of Occurrences

Name 1 Facility A 12/1/2020 Name 1 ???

Name 2 Facility A 12/1/2020 Name 2 ???

Name 3 Facility A 12/2/2020 Name 3 ???

Name 1 Facility A 12/15/2020

Name 4 Facility B 12/3/2020

Name 5 Facility B 12/5/2020

Name 4 Facility B 12/5/2020

Sorry, it pushed all my columns together. Trying again

Example Data Set ------------------------------------ UNIQUE(FILTER(A:A,B:B="Facility A")) --- # of Occurrences

Name 1 --- Facility A --- 12/1/2020 ------------- Name 1 --- ???

Name 2 --- Facility A --- 12/1/2020 ------------- Name 2 --- ???

Name 3 --- Facility A --- 12/2/2020 ------------- Name 3 --- ???

Name 1 --- Facility A --- 12/15/2020

Name 4 --- Facility B --- 12/3/2020

Name 5 --- Facility B --- 12/5/2020

Name 4 --- Facility B --- 12/5/2020

Hi,

I'm trying to get all unique values from a list of entries across several columns, is there a way to do this and return in one column?

For example, if you had the following lists: Customers for product X. Customers For product Y. Customers for product Z.

And you want one simple list of all unique customers?

Thanks,

Matt

I have this same question...

Did you find a solution?

Hello!

Perhaps this comment will be useful to you.

Hello,

I am using this formula to return unique values (text) from 1 column based on a criterion (also text) in another column:

UNIQUE(FILTER(array, criteria_range = criteria))

The array column has cells that are either blank or contain "R". In the case when the criterion is met for several entries in the array column (i.e. several blank cells and may 1 or 2 cells with "R"), the formula returns two lines - both "0" and "R".

How do I remove the "0" and get the formula to ignore blank cells. I have tried the following with no change to the end result:

UNIQUE(FILTER(array, criteria_range = criteria,""))

Thank you very much,

Kirsty

Hello!

If I understand your task correctly, the following formula should work for you:

=UNIQUE(FILTER(A1:A10, (ISNUMBER(SEARCH("r",A1:A10,1))) * (A1:A10<>"")))

Hope this is what you need.

If using the UNIQUE formula to return unique values from single column of data, is it possible to have the function leave a blank cell between each value? Such that is returns the unique data results into every other row

Hi!

I use excel advanced filter to extract unique records only and copied them at another location. However, this feature does not dynamically update the extracted values if a value is changed in the source column. How can I achieve it? Thanks very much.

Hi,

When you copy unique records to another workbook, you are copying values, not formulas. Therefore, these records will not change.

You can create a pivot table and use the Remove Duplicates tool in it.

Please have a look at this article — Excel slicers for pivot tables.

I hope it’ll be helpful.

I have data that is is updated via an external data source. It is a table. I have created several data slicers to make it easier for the users to filter. (5 slicers). I want to include a summary above the data that displays what has been filtered. I tried using the unique function but it pulls all unique values from the whole list and nit just the filtered list. Any thoughts on this? I’ve googled it but have yet to find a solution. Regards Mark

Hello!

Unfortunately, without seeing your data it is difficult to give you any advice.

You may find the FILTER function useful.

Please have a look at this article — Excel FILTER function - dynamic filtering with formulas

If there is anything else I can help you with, please let me know.

First off , thank you for publishing such a comprehensive article regarding the Unique formula.

I am having an issue where I am using the UNIQUE(FILTER(array, criteria_range = criteria)) and it works fine if there are multiple items in the source list meeting the criteria, however when there is only 1 item in my list that fulfills the criteria , it gets repeated infinitely in my output list. Can you please let me know what am I doing wrong?

Please see my example below written as UNIQUE(FILTER(Name,Outcome=1):

Option 1 Option 2

Name Outcome Name Outcome

Angela 1 Angela 0

Andrew 1 Andrew 1

Mike 0 Mike 0

Tom 0 Tom 0

Lisa 0 Lisa 0

Ben 1 Ben 0

Result Option 1 Result Option 1

Angela Andrew

Andrew Andrew

Ben Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

Andrew

.....

My apologies,

I think I figured out what I was doing wrong. I was dragging the array formula down however this function populates automatically from the first formula cell without having an expression in the next cell. All fixed, thank you.

Is it possible to use the "Unique" formula getting unique values with input from several columns, even sitting in different sheets. Let's say I have a column with names in column A sheet1 and another set of names in column B in sheet2 and like to combine them and only see unique names?

Hello!

The UNIQUE function can only select unique values from one range. To select unique values from two ranges, you can use this array formula:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")

List1 and List2 - named ranges with data. You can also use regular absolute references.

The formula is in cell D1. Otherwise change the cell addresses $D$1:D1.

Press Ctrl + Shift + Enter so that array function to work.

After that you can copy this formula down along the column.

I hope I answered your question. If something is still unclear, please feel free to ask.

Thanks, it works, but unfortunately this array function needs more processsing than the Unique function so processing time for the number of rows used is too long. Thanks amyway for calrifying the limitations of the UNIQUE function!

Hello,

Thank you so much for the above! This solves my problem.

It does pick up 1 blank cell though between List 1 and List 2. Do you know how can I get rid of that please?

Thank you,

Hello!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you.

What if I have a table with 4 columns. Columns 1 and 3 shows names and columns 2 and 4 have values. The names in columns 1 and 3 are similar. I just want to add the values on columns 2 and 4 that correspond to the similar name in columns 1 and 3

Hi,

I’m sorry but your task is not entirely clear to me.

Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

I have external data imported from a csv file and need to present results of UNIQUE or FILTER functions performed on that data in an Excel table. However it returns #SPILL error in the table. Is there a way aorund it?

Hello!

The UNUQUE function displays the results in the current column. To do this, she needs a number of empty cells at the bottom. The #SPILL error means that there are not enough empty cells to display unique values. Delete the values from the cells at the bottom, or select a different column for the formula.

I have a list of clients in columns for each month. (See below)

January ----- February ------ March

John ---------- Fred ------------- Mary

Fred ---------- Joe --------------- John

Is there a way to pull a unique list of clients in one row?

John

Fred

Mary

Joe

Hello!

Assuming your values are in the range A2:C9, enter the following formula in cell E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

In this formula, A2:C9 indicates the range of cells from which you want to get unique values. E1 is the first cell in the column where you want to place the result. $2:$9 means rows containing the data you want to use. $A:$C points to the columns containing the data you want to use. Please change them to your own.

This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter. After that you can copy this formula down along the column to extract unique values until blank cells appear.

Is it possible to extract unique values in a column and list the values across a set of row. i.e transpose the unique values?

Hello!

If I understand your task correctly, you can use the TRANSPOSE function:

=TRANSPOSE(UNIQUE(C1:C16,FALSE,FALSE))

I hope it’ll be helpful.

Perfect, thank you.

Great information here. Do these formulas allow use of wildcards?

=IFERROR(UNIQUE(FILTER(A2:B10, C2:C10=F1)),0) The formula works but only for the exact

F1= abc. need any C column that includes all instances of abc, ex. xyx abc, abc xyz, xyz abc def is needed. I tried using * and + incorporating "*"&F1&"*" but results returned 0.

Hello!

If I got you right, the formula below will help you with your task:

=UNIQUE(FILTER(A2:B10,IFERROR(SEARCH(F1,C2:C10,1),FALSE)))

You can learn more about SEARCH function in Excel in this article on our blog.

Thank you for the rapid reply and article link for the Find and Search functions and examples for Excel 2019 and 365,

Hello each of these filter formulas work alone but not by combining with and *:

=ROWS(UNIQUE(FILTER($C$2:$C$882,IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882),FALSE))))

=ROWS(UNIQUE(FILTER($C$2:$C$882,IFERROR(SEARCH("*"&D883&"*",$D$2:$D$882),FALSE))))

does not work:(too few arguments) =ROWS(UNIQUE(FILTER($C$2:$G$882,(IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882)*($D$2:$D$882,IFERROR(SEARCH("*"&D883&"*")),FALSE))))

Hello!

Sorry, it's not quite clear what you are trying to achieve.

What do you want to calculate exactly? Your question is not entirely clear, please specify.

I want to use the two formulas combined as one. The section above titled "Filter unique values based on multiple criteria" mentions the basic formula =UNIQUE(FILTER(A2:B10, (C2:C10=G1) * (D2:D10<G2)))

Intend to count unique rows using filters for instances that contain "*g883*"(G1) and(*) "*d883*"(G2).

column d is a smaller subset of g.

both formulas start the same: ROWS(UNIQUE(FILTER($C$2:$C$882,,,,

first formula includes : (IFERROR(SEARCH("*"&G883&"*",$G$2:$G$882) which works.(G1)

second formula includes: (IFERROR(SEARCH("*"&D883&"*",$D$2:$D$882) which works(G2)

however, combining with * results in error: you entered too few arguments for this function. Thank you for your response and effort to find a solution.

Hi!

You are using too many arguments in the FILTER function. I am assuming that you want to apply a filter to two ranges and combine them. It is impossible to do this with one function.

I do not see your data and your explanations are not very clear. I do not have enough information to offer you a solution.

Hi, I need a way of being able to count unique strings within a cell in a range and im not entirely sure how. For example if my data set was below, what formula would i use to count the unique instances of the numbers 1-20 for each agenct? The subset of the data for agent A is below the main set below, and i manually count 14 different strings amongst the total data. Those 14 are spread across 6 different instances of Agent A. So i cant count column 1 for agent A, i cant cound column B for uniques... My only thought at this stage is to set up a new table matching 1-20 against Agents A-B, and then doing a manual count for included numbers (count how many times each number shows for each agent), and then doing a sumif >0 on that table.

Is there a more elegant solution?

Agent B 1

Agent B 10, 19

Agent B 17

Agent C 17

Agent A 5, 13

Agent C 5, 7, 17

Agent A 1, 5, 11

Agent B 11

Agent C 7, 20

Agent C 11

Agent A 6, 18

Agent A 1, 2, 5, 10, 15

Agent D 1

Agent B 9

Agent D 16, 17

Agent A 9

Agent A 8, 13, 18

Agent B 7, 13

Agent B 8, 12, 16

Agent B 13, 15, 16

Agent C 9, 17

Agent B 2, 5

Agent B 1, 4, 15

Agent C 13, 19

Agent A 5

Agent C 9

Agent D 8

Agent D 4, 9, 12

Agent C 1, 10, 17, 18

Agent A 5, 7, 9, 11, 12

Agent D 7, 12

Agent C 9

Agent B 7, 19

Agent D 1, 7

Agent D 18, 20

Agent D 13, 14

Agent D 3

Agent B 5, 6, 9, 12

Agent A 6, 18

Agent A 1, 2, 5, 10, 15

Agent A 9

Agent A 8, 13, 18

Agent A 5

Agent A 5, 7, 9, 11, 12

Hello!

I believe the following formula will help you solve your task:

=SUMPRODUCT(--(FREQUENCY(FILTER($B$2:$F$45,$A$2:$A$45="Agent A"),FILTER($B$2:$F$45,$A$2:$A$45="Agent A"))>0))

Each number is written in a separate cell.

If your numbers are written in one cell as text, then use the recommendations of this article to split the numbers into cells.

We have a ready-made solution for your task. The Split Text tool is part of Ultimate Suite for Excel. With its help you can separate a column of text into multiple columns or rows. Easily split data by any character, string, or mask.

You can install it in a trial mode and check how it works for free.

Very helpful, never knew the unique command existed, combining with sort is magic.

Thank you so much.

Very useful! But as people above have said, when trying to use this in a Excel Table I get a #SPILL! error. I am trying to make a dynamically updating Table that will add rows as my source data updates. It works fine until I convert the range to a Table. Am I missing something?

Hello!

If the formula should write the result to a range of cells, but those cells are not empty, then #SPILL! error

Hi Tom,

Unfortunately, dynamic array functions (and UNIQUE is one of them) do not work from within Excel tables, they can only be used within regular ranges.

Oh that's a shame! Thanks for confirming though, I thought it was just me.

hi I am trying to extract unique values from two worksheets within the same workbook into another worksheet which is also in the same workbook. How can I do that?

example:

worksheet 1 has:

aaaa

bbb

ccc

ddd

Worksheet 2 has:

aaaa

ccc

eee

The result should be:

aaaa

bbb

ccc

ddd

eee

is this possible via formula? Please let me know thanks

Hello!

Unfortunately, this cannot be done using a formula.