## How to extract a unique distinct list from a column in excel

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website.

I would choose the advanced filter if you want to quickly create a unique distinct list. It is fast and easy.

If you know that you will be extracting unique distinct values from time to time in the same workbook I would recommend using a formula and an excel defined table. You won't need to repeat the same steps over and over compared to the advanced filter and that will save you time and repetitive work.

However working with a large data set may slow down the formula calculations considerably depending on your computer hardware, so perhaps the User Defined Function [UDF] is a better choice or even better a pivot table, if you have huge amounts of data to work with.

The pivot table is lightning fast even with huge data tables but it does have a little learning curve and it requires a few steps to set it up but in my opinion it is totally worth learning how to use pivot tables.

I have made an add-in that lets you extract unique, unique distinct and duplicate values and records from multiple worksheets. This allows you to easily bring together data from multiple sources in your workbook.

**Table of Contents**

- How to extract unique distinct values from a column [Formula]
- Extract unique distinct values (case sensitive) [Formula]
- How to filter unique values from a list [Formula]
- How to extract unique distinct values from a column using advanced filter [Excel feature]
- User Defined function: Extract unique distinct sorted values from a cell range (vba) [UDF]
- Build a list of unique distinct values [Pivot Table]
- Filter unique distinct values and records from multiple sheets add-in
- Useful tips
- Useful links
- Functions in all above formulas

### Create a list of unique distinct values

First, allow me to explain what unique distinct values are. Unique distinct values are all cell values but duplicate values are removed. To filter duplicates, read this post: Extract a list of duplicates from a column

The picture below shows a list of values, AA has a duplicate. There are no duplicates in the next column "Unique distinct values".

**Example sheet - How to remove duplicate values
**

Column A contains names, some cells have duplicate values. An array formula in column B extracts an unique distinct list from column A.

**Update: 2017-08-15!**

*This formula is even smaller than the array formula and you are not required to enter this as an array formula.*

**Formula in cell B2:**

The formula above extracts a list in no particular order. The following link demonstrates how to filter a unique distinct list, sorted alphabetically:

Create a unique distinct alphabetically sorted list, extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel, Sorted unique distinct values

The formula above has an issue with blank cells, it returns a 0 (zero) in your list. This article shows you how to avoid blanks:

Filter a unique distinct list and remove blanks

Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains […]Comments(24) Filed in category: Excel, Unique distinct values

**Array formula in cell B2:**

*Thanks to Eero, who contributed the original array formula!*

This article shows you how to use a condition to filter unique distinct values:

Vlookup – Return multiple unique distinct values in excel

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Lean how to filter values with a condition and return alphabetically sorted unique distinct values:

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]Comments(1) Filed in category: Excel, Sorted unique distinct values, VLOOKUP and return multiple values

#### How to create an array formula

You don't need to follow these steps if you chose the regular formula.

- Copy the aray formula above (Ctrl + c)
- Double click cell B2
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:

{=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))}

Don't enter these characters yourself, they appear automatically.

Copy cell B2 and paste to cells below as far as needed.

Recommended reading:

Filter unique distinct row records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]Comments(2) Filed in category: Excel, Unique distinct records

#### How the array formula in cell B2 works

**Step 1 - Create an array with the same size as the list**

=INDEX($A$2:$A$20,MATCH(0,**COUNTIF($B$1:B1,$A$2:$A$20)**,0))

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given condition

COUNTIF($B$1:B1,$A$2:$A$20) returns an array containing either 1 or 0 based on if $B$1:B1 is found somewhere in the array $A$2:$A$20.

COUNTIF($B$1:B1,$A$2:$A$20)

becomes

COUNTIF("Unique distinct list",{Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas} )

and returns:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

This means the cell value in $B$1:B1 can´t be found in any of the cells in cell range $A$2:$A$20. If it had been found, somewhere in the array the number 1 would exist.

Recommended reading:

Comments(5) Filed in category: Excel, Functions

**Step 2 - Return the position of an item that matches 0 (zero)**

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$1:B1,$A$2:$A$20),0)

becomes

MATCH(0,{**0**;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

and returns 1.

Recommended reading:

Comments(12) Filed in category: Excel, Functions

**Step 3 - Return a cell 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.

*=INDEX(List,1) *

becomes

=INDEX({**Federer,Roger**; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas}, 1)

*and returns "Federer, Roger"*

Recommended reading:

Comments(14) Filed in category: Excel, Functions, Index

**Relative and absolute cell references**

When you copy the array formula down the countif formula range (*$B$1:B1)* expands. This is created by using relative and absolute references.

The first cell, B2: *COUNTIF($B$1: B1,$A$2:$A$20)*

Second cell, B3: *COUNTIF($B$1: B2,$A$2:$A$20)*

and so on.

Recommended reading:

Absolute and relative references in excel

What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]Comments(12) Filed in category: Excel

#### Excel 2007 users can remove errors using IFERROR() function

When the formula runs out of values it returns #N/A errors (Not Available), you can use the IFERROR function to remove the error and return blank in those cell.

Unfortunately it comes with a big caveat, it also removes other formula errors as well. So use this with great caution. If your source table has errors you won't detect it because the IFERROR function returns a blank cell instead.

Array formula in cell B2:

*and copy it down as far as necessary.*

The formula is an array formula, how to enter an array formula.

Recommended reading:

Comments(0) Filed in category: Excel, Functions

#### Excel 2003 users can remove errors using isna() function:

*and copy it down as far as needed.*

This formula is an array formula, how to enter an array formula.

#### How to handle blank cells in a range

Harlan Grove created a formula to count unique distinct values from a list with blanks. I used the same technique here to filter unique distinct values:

Array formula in cell B2:

#### Download excel sample file for this tutorial

Extract-a-unique-distinct-list-in-excelv4.xlsx

(Excel 2007 Workbook *.xlsx)

#### Unique distinct formulas

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]Comments(78) Filed in category: Excel, Unique distinct values

Extract a unique distinct list from three columns in excel

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]Comments(31) Filed in category: Excel, Unique distinct values

### Extract a unique distinct list (case sensitive)

The following array formula lists unique distinct values from a list and it is case sensitive. Aa is not equal to AA.

**Array formula in cell C2:**

Recommended reading:

Search case sensitive and return multiple values in excel

Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and […]Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values

#### Explaining the array formula in cell C3

**Step 1 - Transpose previous values**

TRANSPOSE($C$1:C2)

becomes

TRANSPOSE({"Unique distinct list (case sensitive)";"Aa"})

and returns

{"Unique distinct list (case sensitive)","Aa"}

Note that the ; changes to a ,

Recommended reading:

Comments(4) Filed in category: Excel, Functions, Transpose

**Step 2 - Check if two text strings are exactly the same, also case sensitive **

EXACT($A$1:$A$9, TRANSPOSE($C$1:C1))

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

and returns

{FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}

**Step 3 - Return relative position in array if TRUE**

IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, {1;2;3;4;5;6;7;8;9})

and returns

{FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE}

**Step 4 - Calculate how often values occur in an array**

FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},MATCH(ROW($A$1:$A$9),ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},{1;2;3;4;5;6;7;8;9})

and returns

{1;0;0;0;0;0;0;1;0;0} Aa is found in position 1 and 8 in cell range A1:A8

**Step 5 - Find first empty value (0) in array**

MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0)

becomes

MATCH(0, {1;0;0;0;0;0;0;1;0;0}, 0)

and returns 2.

**Step 6 - Return value from position 2**

INDEX($A$1:$A$9, MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0))

becomes

INDEX($A$1:$A$9, 2)

and returns "CC" in cell C3.

**Download excel *.xlsx file **

Extract-a-unique-distinct-list-in-excel4.xlsx

#### Articles with topic: Case sensitive

**Filter unique distinct records (case sensitive) [UDF]**

A User Defined Function to extract unique distinct records (case sensitive).

**Search case sensitive and return multiple values**

Do a case sensitive VLOOKUP and return multiple values, however the VLOOKUP function is not used here.

**Count unique distinct values (case sensitive)**

This post demonstrates a formula on how to count unique distinct values (case sensitive)in a cell range

**Count multiple text strings in a cell range (case sensitive)**

This post demonstrates a formula on how to count text strings (case sensitive) in a cell range

**How to filter unique values from a list**

Unique values are values existing only once in a list. Example, AA occurs twice in the list below and is not unique. BB and CC occur only once each and is unique in the list.

**Example sheet - Filter unique values**

Column C filters all unique values values from column A. Unique values occurs only once in column A.

Example, Roger, Federer is not in column C because there are more than one value in column A. In other words, the name is not unique in column A. You can find the name twice in the list, in cell A2 and A8.

**Array formula in C2:**

or use this regular formula:

*and copy cell C2 down as far as needed.*

#### Explaining array formula in cell C2

**Step 1 - Count each value in array and check if it is not equal to one**

(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}<>1

and returns

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

This array tells excel that the first value in the array is not unique and that is true because Roger, Federer is not unique in the list. However the second value is FALSE and that value is unique, etc.

**Step 2 - Keep track of previous values**

C1:$C$1 is a dynamic cell reference, it changes as the formula is copied to cells below. You can read more about absolute and relative cell references here.

COUNTIF(C1:$C$1, $A$2:$A$20)

becomes

COUNTIF("Unique list", {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

A zero (0) means that no values have yet been displayed and that is true in cell C2. However when excel calculates the value in cell C3, cell C2 shows "Djokovic, Novak" and the array becomes {0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}. The second value in the array contains 1. This tells excel that value has already been shown.

**Step 3 - Add arrays**

COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} + {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}

TRUE is 1 and FALSE is zero. So True + 0 equals 1 and False + 1 equals 1.

**Step 4 - Find first zero value in array**

A zero in the array indicates {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0} that the corresponding value is unique and has not yet been displayed in the list.

MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0)

becomes

MATCH(0, {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, 0)

and returns 2.

**Step 5 - Return corresponding value**

INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0))

becomes

INDEX($A$2:$A$20, 2)

and returns "Djokovic, Novak" in cell C2.

#### Download excel sample file for this tutorial.

Extract-a-unique-distinct-list-in-excel.xls

(Excel 97-2003 Workbook *.xls)

To extract duplicates, see this post: Extract a list of duplicates from a column using array formula in excel

Back to top

### How to extract unique distinct values from a column using advanced filter

Unique distinct values are all cell values but duplicate values are merged into one distinct value.

- Go to tab "Data" on the ribbon
- Click "Advanced Filter" button on the ribbon

- Click "Copy to another location"

- Click "List range:" and select range to filter unqiue distinct values
- Click "Copy to: and select a range
- Click "Unique records only"
- Click "OK"!

#### Lean more about other powerful excel features

Advanced filter is not the only powerful built-in feature in excel, here are some more:

**Excel Pivot table**

This post shows you how pivot tables work. Pivot tables are perhaps the most powerful feature in excel and also the least known.

**Excel defined tables**

Excel defined tables lets you organize, filter and manipulate data with ease.

### User Defined function: Extract unique distinct sorted values from a cell range (vba)

This UDF lets you create and sort a unique distinct list. First you need to copy the VBA code to your workbook, instructions below. Second, select a cell range. Third, type FilterUniqueSort(*cell_ref*) in the formula bar. Last, enter formula as an array formula, instructions below.

There is also a workbook for you to download.

**Array formula in cell B2:B8212:**

**How to create an array formula**

- Type B2:B8212 in name box
- Type above array formula in formula bar

- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**VBA code**

I am using the selectionsort function to sort values. You can read more about the function here:

Using a Visual Basic Macro to Sort Arrays in Microsoft Excel

Function FilterUniqueSort(rng As Range) Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single ReDim temp(0) On Error Resume Next For Each Value In rng If Len(Value) gt; 0 Then ucoll.Add Value, CStr(Value) Next Value On Error GoTo 0 For Each Value In ucoll temp(UBound(temp)) = Value ReDim Preserve temp(UBound(temp) + 1) Next Value ReDim Preserve temp(UBound(temp) - 1) iRows = Range(Application.Caller.Address).Rows.Count SelectionSort temp For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i FilterUniqueSort = Application.Transpose(temp) End Function

**Where to copy vba code?**

- Press Alt + F11 to open VB Editor
- Click "Insert" on the menu
- Click "Module" to create a module
- Copy (Ctrl + c) above vba code and paste (Ctrl +v) to the code module

**Download example file**

Extract unique distinct sorted values from a cell range.xls

#### More powerful UDFs

**Filter unique distinct records (case sensitive) [UDF]**

A User Defined Function to extract unique distinct records (case sensitive).

**Lookup and return multiple values concatenated into one cell [UDF and formula]**

Use a condition to extract values and concatenate them.

**Filter unique distinct words from a cell range [udf]**

Extract only unique distinct words from a cell range

### Filter unique distinct values from multiple sheets add-in

**Filter unique distinct values **is an add-in for Excel 2007/2010/2013 that lets you extract

- unique distinct values
- duplicate values
- unique distinct records
- duplicate records

from **multiple sheets**. The Add-In contains 4 user defined functions.

If a value in one of the ranges changes the function will automatically and instantly update the list.

#### Features

- All user defined functions remove blank values and blank records.
- No error values when all values are extracted.
- Filter values or records from up to 255 different cell ranges or sheets.

#### What are unique distinct values?

#### What are unique distinct records?

#### Example - How to filter unique distinct values from multiple sheets using the Add-In

Purchase Filter Unique Distinct Values From Multiple Sheets Add-in For Excel 2007/2010/2013 - Price $19 USD

### Questions

**Is there a money back guarantee?**

Sure, you have un-conditional money back guarantee for 14 days.

### Useful tips

#### Excel tables

An Excel defined table is very cleverly designed, it is constructed to automatically expand if you add more data. You don't need to do anything.

Structured references are cell references to an excel defined table. They let you easily see what the data contains as long as you give it good descriptive column header names.

I recommend you use excel defined tables instead of named ranges or dynamic named ranges as long as you are working with more than one value.

Here is how to convert a list to an excel defined table:

- Select a cell in your list
- Go to tab "Insert" on the ribbon and click Table button or press Ctrl + T
- Click OK button
- Your excel defined table is created

Read more about excel defined tables

#### Named ranges

In excel you can name a cell range, a constant or a formula. You can then use the named range in a formula, making it easier for you to read and understand formulas.

*Example*

List : A2:A20

Tip! Use dynamic named ranges to automatically adjust cell ranges when new values are added or removed.

#### How to create a named range

The downside with named ranges is that you need to adjust the range every time you add or delete a value in the list, the named range will then not fit the value list. I recommend using excel defined tables if you know that the list may change in the future.

- Select cell range A2:A20
- Type
*List*in name box - Press Enter

**Array formula and named range in cell B2:**

#### Useful blog posts

Want to learn more about **filtering unique distinct values**? You must read these blog posts:

- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Unique values from multiple columns using array formulas
- 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

Learn more about **sorting** unique distinct values. Read these blog posts:

- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Create a unique list and sort by occurrances from large to small

Learn more about **counting unique distinct values and records**. Read these blog posts:

Learn more about **filtering** and **comparing** unique distinct records. Read these blog posts:

- Filter unique distinct records in excel 2007
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Filter common row records in excel
- Quickly compare two tables in excel 2007

Read more about **custom functions** in excel

#### Functions in all above array formulas

**INDEX function explained**

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

**COUNTIF function explained**

COUNTIF(

*range*,

*criteria*)

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.

**MATCH function**

MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of an item in an array that matches a specified value in a specific order

**How to use the IFERROR function**

IFERROR(value, value_if_error)

Returns value_if_error if expression is an error and the value of expression itself otherwise.

**IF function explained**

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

**SUM function explained**

Learn how to use Excel SUM function and it's arguments. The SUM function adds all the numbers in a range of cells. Many examples and a workbook for you to download.

**How to use FREQUENCY function**

FREQUENCY(

*data_array*,

*bins_array*)

Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array

**TRANSPOSE function**

TRANSPOSE(

*array*)

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range is a range with values in one column, a horizontal range has values in one row. You can also transpose a range with more than one column and one row.

**EXACT(**text1,[text2],**)
**Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case sensitive.

### Category: Advanced filter excel

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

Comments(14) Filed in category: Advanced filter, Excel, Unique distinct values, VBA

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]

Comments(6) Filed in category: Advanced filter, Excel, Unique distinct values

Question: How do I filter the last xx years or xx months in Excel 2007? How do I exclude the […]

Comments(4) Filed in category: Advanced filter, Dates, Excel

### Category: Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Thanks a lot!!! You've made my day!

Hey there - thanks a lot for this tip - though I initially could not get this to work - eventually I figured out MY problem the INDEX rownum was using "ROW(A2:A20)-1", when it should be using "ROW()-1" - otherwise it ALWAYS returns the start of the array.

Thank you for visiting Get Digital Help and commenting! I have changed the formula and the attached file. Using named ranges simplifies formula customization a lot!

Many thanks for the useful information posted on this website! However, as I am working on a very large set of data (from between 50 - 8000 records) and whenever I change the name range to go beyond the 1000th record, the unique list value would return error. I have tried to name the whole column A but it also doesnt work.

would be grateful if you could assist on this.

Many thanks for all your effort!

Thank you!

I tried to recreate your error, but failed. I have attached an excel file with over a 1000 records that works for me. The excel file is at the end of the above blog post. Try it and see if the excel file returns an error.

I am using excel 2007, what version are you using?

Have you changed cell ranges for both named ranges in your excel sheet?

List (A2:A20)

unique_start (B2)

Maybe you have a blank cell somewhere in your list? A blank cell returns an error. See this post:

https://www.get-digital-help.com/2009/03/10/how-to-automatically-create-a-unique-list-and-remove-blanks/

There is a somewhat shorter approach:

Let initial items in colA be named as "List". In B2 enter the

following array-formula:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) and copy it down as far as necessary, to get unique list.

That is really amazing, you made the array formula even shorter!

To remove any errors, Excel 2007 users can use IFFERROR() function:

=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"") + CTRL + SHIFT + ENTER and copy it down as far as necessary.

Thank you for your valueable comment!

hi.

thanks for the formulas (eero's and the revised one) guys. it really cured my headaches.

for the unique list generated (Column B), can the...

1) unique list sorted A-Z order?

2) unique list sorted based on occurrence (highest to lowest)?

the trick is: can the sorting be done without complicating eero's formula above?

reason i'm asking because the other posts on sorting is too complicated for my head to wrap around ;)

thx!

Interesting questions!

I´ll see if I can come up with some good solutions.

Thanks for commenting!

thanks!

in case you're wondering, eero's formula does not need the unique list range (Column B) to be same size as the main list (Column A).

I just need to copy down the formula to get the rest of the unique list.

Your other posts on sorting is based on range-based array formula (i.e. Column B must be same size as Column A in order for the array formula to work). This works well if you have short list, but if u have thousands of entries, unique list range must equal the same.

One other that thing that i noticed is the ranged-based array formula is fixed (you can't delete those 'empty' cells in this range).

anyways, thanks again for looking into another solutions and not to forget your valuable blogs here!

David, here is the answer to your first question.

unique list sorted A-Z order?Array formula in cell B2, see picture above.

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)) + CTRL + SHIFT + ENTER and copy it down as far as necessary, to get unique list sorted alphabetically from A to Z. If anyone can come up with a shorter formula, i would be happy!

oh wow! it works! :)

THANKS!

David, here is the answer to your second question.

unique list sorted based on occurrence (highest to lowest)?Array formula in cell B2, see picture above.

=INDEX(List, MATCH(IF(MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))=0, 1, MAX(COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1))), COUNTIF(List, List)*IF(COUNTIF(B$1:$B1, List)=1, 0, 1), 0))+ CTRL + SHIFT + ENTER and copy it down as far as necessary, to get sorted based on occurrence (highest to lowest)

I have to think some more to get this formula shorter, I don´t like the length of it.

works great! :D

thanks!

p/s: as long as the array formula works, i'm happy. but do keep up your great job on the formulas! MS Excel gurus should visit and give you a thumbs up! :)

Wow the short formula works, amazing!!!

Thanks!

Wow! This is just amazing. Works great. I would love if someone could explain how this works step by step. I tried, but couldn't. I am trying to extend this fomula with a user defined formula.

[...] How to extract a unique distinct list from a column in excel [...]

Sriram Venkitachalam,

I now have tried to explain the array formula, see the new section above.

Thanks for commenting!

Would you please advise me on how to created named range which includes A-Z sorted list of UNIQUE values of other list?

Thanks a lot for this example. But is there also a way without using CSE? because a use a tool where i import Excel file which does not support CSE formulas in Excel.

Hopefuly someone has an idea

Dear All, It's my first post right here, and I'd like to thank you all with special THANKS for ADMIN. Well, I'm looking for more info and explanation about the current topic of https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ It has mentioned above,

"How this array formula works: =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) The array formula uses MATCH() to find the first 0 (zero) value in the COUNTIF array. When you copy the array formula down the COUNTIF formula range expands. The first cell: COUNTIF($B$1:B1,List), the second cell: COUNTIF($B$1:B2,List) and so on"

It's understood, but I'm still puzzled for the following reasons:

1. When applying the mentioned formula, the result is always the first record only.

2. All array elements shows INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) in formula bar, which indicates that the range expansion mentioned above is not applicable.

3. The attached example sheet shows

@ cell B2: INDEX(List,MATCH(0,COUNTIF(B1:$B$1,List),0)),

while @ cell B3: INDEX(List,MATCH(0,COUNTIF(B$1:$B2,List),0)),

and It’s OK, if we are talking about singular cell formula.

So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?

Do I miss something?

Is there any Excel settings should be takes place?

Please help & advise.

Thank YOU & BEST Regards,

Raymond,

You need to convert your formulas to values.

Copy and "Paste Special.." the values created by the CSE formula.

Select "Values" and click OK.

OnlineAlone,

Copy =INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)) + CTRL + SHIFT + ENTER into your first cell. (Cell B2)

Copy your first cell to the remaining cells.

The formula then automatically changes to:

Cell B3: =INDEX(List,MATCH(0,COUNTIF($B$1:B2,List),0))

Cell B4: =INDEX(List,MATCH(0,COUNTIF($B$1:B3,List),0))

and so on.

Oscar,

Thank you for your quick response.

OK, I got it.

OnlineAlone,

I missed this question:

So how come (B1:$B$x) changed into (B$1:$Bx) from cell to another within the same array command?

You have found an error in the attached file.

I have now changed the formula in the attached file and uploaded it to this blog post again.

Here is an excellent explanation of absolute and relative references:

https://www.cpearson.com/excel/relative.aspx

hi all,

thanks for the great formula/array formula. it works great.

lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).

e.g.

1-Jan-2010 ProductA

1-Jan-2010 ProductB

1-Jan-2010 ProductC

1-Jan-2010 ProductD

2-Jan-2010 ProductA

2-Jan-2010 ProductB

2-Jan-2010 ProductC

...

...

the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.

In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.

the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?

thx!

David,

have you tried "Advanced Filter"?

https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#advfilter

Thanks for commenting!

2 problems i see using Advanced Filter:

a) The first 2 generated entry is a repeat. i.e. 1-Jan-2010, 1-Jan-2010, 2-Jan-2010, 3-Jan-2010, and so on.

first entry is named “Extract”).

b) The dates from my master table is set with a dynamic named range. Advanced Filter will generated unique

distinct list, manually.

I daily update the table

thx!

david,

See this blog post (and the attached file):

https://www.get-digital-help.com/2010/02/10/create-a-unique-distinct-list-of-a-long-list-without-sacrificing-performance-using-vba-in-excel/

just noticed this standalone post. incredible.

will try it out.

note: reason why Advance Filter is not preferred (by me anyway) is the list will gets longer daily. i wouldn't want to keep (manually) using Advanced Filter.

'sides, i'm preparing the file for Excel noobs. he/she should not be complicated by the steps :D

the VBA macro button is interesting tho! will try it now!!

thx guys!!

I been trying to do this for a long time. I tried by pivotTable to extract the list without duplicate items.

This much easier, clear, and great! Thanks a lot!

I found out the column 'A' must choose the title of it 'A1' also, to avoid in column 'B' shows 'A2' twice, and there is no problem to choose 'A1' coz it shows the same title in 'B1'.

Thanks again!

In excel 2003 and older you can remove error by using ISNA() function.

The formula is brilliant but it took me some significant amount of time to understand it (use Evaluate formula).

Wiciu,

Thanks!! I have added the ISNA() function to this post.

/Oscar

Oscar, these countif function formulas are great because they are able to extract a list with blank cells in the range. Is there a way to extract a list using sum(if instead of countif because this will work with closed workbooks (when the need arises), while also having the ability like the countif function to handle blank cells in the range? Maybe we need to put a condition in the front of the formula to handle the blank cells. Keep up the brilliant work. This website is one of the top Excel websites out there.

Sean,

Thanks!

As far as I know, you can´t extract a unique distinct list from a closed workbook with excel formulae.

Sean,

See this post: https://www.get-digital-help.com/2010/05/10/extract-unique-distinct-numbers-from-closed-workbook-in-excel-formula/

Sean,

Here is another post: https://www.get-digital-help.com/2010/05/12/extracting-unique-distinct-text-values-from-a-closed-workbook-in-excel-formula/

Oscar,

Thanks for the great article! Is it possible to modify the array formula to return the entire reduced list in a single cell? Instead of taking up as much real estate as the original list. For example the function '=offset(A1,0,0,10)' would return a list 10 numbers long in one cell. Not too useful in that cell, but I can then refer to only that cell for a data validation list or use it to do additional processing to.

Trey,

Data Validation was designed to work with lists of cells. I don´t know how to solve your problem.

I suppose another way to word it would be, I'm looking to return a list. The result of the above formula is a single value. Can array functions return a list instead of a single value? I am guessing, but isn't the result of typing "=Offset(A1:A10,0,0)" into B1 a list in cell B1? If your formula could output a list into one cell (say B2 from the example), a Data Validation reference could be made to just B2 instead of B2:B20.

As far as I know, "=Offset(A1:A10,0,0)" + CTRL + SHIFT + ENTER creates an array. You would only see the first value in cell B1. Creating a data validation reference to cell B1 won´t work. Only the first value is used.

Can array functions return a list instead of a single value?

Yes, but not in a single cell.

THANK YOU THIS HELPED. IT TOOK ME A WHILE TO FIGURE OUT... WHAT REALLY HELPED WAS BEING ABLE TO DOWNLOAD THE EXCEL FILE SO THANKS!

I tried to use the =IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))) + CTRL + SHIFT + ENTER and copy it down as far as needed.

Its not working. I get a blank cell only. Could you give some guidance. Working with excel 2003.

Thanks,

Greg

I forgot to mention I do have blank values and that the list is on a different sheet than what the formula would be does this cause a problem?

Thanks again,

Greg

Greg,

Yes, I think that causes a problem.

See this post: https://www.get-digital-help.com/2010/07/11/create-a-unique-distinct-sorted-list-containing-both-numbers-text-removing-blanks-in-excel/

Thanks for your comments!

Greg,

This is how I handled blanks in a range with this formula.

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))

Oscar,

I use this formula a lot. By adding conditions in front of the countif, I can extract a list with numbers, etc.

Is there a way to extract a list from Column A based on the total for this value being above say 30 in column B? I have been trying to use Sum(if to make it work without any success. The result below would be CC.

aa 10

aa 15

cc 40

cc 5

Sean,

That is the formula I should have written as an answer. Great contribution! I have added the formula to this post.

THANKS!

Extract a unique distinct list from column A where the total for this value in column B are above 30:

Array formula in cell C2:

=INDEX(list, MATCH(0, IF(SUMIF($A$1:$A$4, $A$1:$A$4, $B$1:$B$4)>30, COUNTIF($C$1:C1, list), ""), 0)) + CTRL + SHIFT + ENTER.

Fantastic Oscar. I was told that sumif cannot coerce a range, which could make the next part more difficult. I am looking for the values that are not equal to zero correct to 2 decimal points. Sometimes the sum is 0.0005, which is zero correct to 2 decimal points.

Is there a way to do this?

Oscar, the sum could be 0.000789 or -0.0000046464. These are rounding differences. Using Sum(if instead of sumif could easily coerce the entire range in memory to 2 decimal points.

Oscar, I figured it out.

INDEX(List, MATCH(0, IF(ROUND(SUMIF($A$3:$A$10, $A$3:$A$10, $B$3:$B$10), 2)0, COUNTIF($E$1:E1, List), ""), 0)).

Dear Oscar,

I try to find out if it is possible to rank the outcome of the most forecoming uniques in the 'Unique distinct list'. Like a top 10 (or 25) list of the most found uniques?

I work here with a huge list, it can be large as +12.000 rows. From this list i want to find out the top 10/25 most forecoming uniques, and ranked them. Most found uniques on top of list.

Sokolum,

First create the unique distinct list.

Use advanced filter to create a unique distinct list. Your list is huge so the formula on this page is too slow.

Read more about advanced filter.

Next to the unique distinct column create a formula to calculate occurences. Somtehing like this: =COUNTIF($A$1:$A$12000, C2) + ENTER. Copy the cell and paste it down to the end of your unique distinct list.

And last, sort the unique distinct list and occurences largest to smallest.

Thanks sean and oscar!

I used: =INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF($B$1:B1,list)),0))

All I get is the NA# error now. All the blanks are at the end of this list name

Thanks,

Greg

I think I mentioned this before but my data is on different sheets and I am using excel 2003.

Thanks,

Greg

These results is exactly i was looking for. Indeed that a formula is slow. Now i will try to find out if i can automate this by a script.

Greg Savinda,

Did you adjust the relative reference? Bolded in formula:

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF(

$B$1:B1,list)),0))Oscar,

I have used the following:

=INDEX(list,MATCH(0,IF(ISBLANK(list),"",COUNTIF(Sheet1!$D$1:D1,list)),0))

Would this error be to the fact that it is in a different sheet?

Thanks,

Greg

Greg Savinda,

I don´t think so.

Did you adjust the named range to your list?

Did you forget CTRL + SHIFT + ENTER?

Is it possible to extend this by matching items that meet a criteria?

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A

Frank Branch A

Frank Branch A

Joe Branch A

Mary Branch B

Jane Branch C

Mike Branch A

Joe Branch A

Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

Anura,

See this post:

https://www.get-digital-help.com/2010/09/01/extract-a-unique-distinct-list-by-matching-items-that-meet-a-criterion-in-excel/

I can't believe the speed of the response! I will test out with some real data from one of my reports and see what happens. Thanks for this.

[...] gospieler says: September 8, 2010 at 10:34 pm This tutorial show you How to extract a unique distinct list from a column in excel (without using advanced filter) https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-... [...]

Oscar,

I have a sheet with 3000 rows of invoice dates that are out of order. There could be a maximum of 6 dates and a minimum of 1 in each row. The invoice dates are in row A1:F1 and I would like the ordered, unique distinct list to be in G1:L1, and also display a blank once the data runs out. How do I alter your formula to accomplish this? Thanks!

Aamer,

Read this post: Excel udf: Remove duplicates from a large dataset

and this post: Remove duplicates and sort dates by each row in excel

The good thing about this formula is that it is short and easy to remember. The main drawback with countif is that it is not able to coerce a range. I would like an unique list based on the last 4 characters in a cell. This would mean using the right function.

1845-2145

1846-2145

1845-2176

The unique list here is 2145 and 2176. Is there a short but sweet formula like the original countif formula above?

Sean,

Values in cell A1:A3:

1845-2145

1846-2145

1845-2176

Array formula in cell B2:

=RIGHT(INDEX($A$1:$A$3, MATCH(0, COUNTIF($B$1:B1, RIGHT($A$1:$A$3, 4)), 0)), 4) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far as needed.

Oscar!

THANK YOU SOOOOOOOOOOOOOOOOOO MUCH!!!! You have no idea how long I've been looking for this!!! I was told it couldn't be done but here it is! You've done it and without using any macros!!!

Many, Many Thanks!!!!

Keep up the AWESOME work!

Charlie,

I am happy you found what you were looking for! Blog reader Eero is the creator of this formula.

Oscar, it works great. I also tried it with the mid function. I thought countif does not work with arrays. The following formula will not work with countif- =TEXT(A1:A3,mmmmyy).

Sean,

Read this post: Remove duplicate text strings based on the 4 last characters in a cell in excel

Is there a way to create a unique distinct list using as criteria the values that do not exist in another column?

COLA ColB

A Z

B A

C B

D C

D X

The result is Z and X.

The formula I have used to create criteria based on another column

=INDEX($B$1:$B$4, MATCH(0, IF($A$1:$A$4="A", COUNTIF($D$4:$D4, $B$1:$B$4)), 0))

Sean,

Array formula in cell C2:

=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.

Oscar,

It works fantastic. Thanks for all your help.

[...] You might notice in Debra's workbooks that the DinnerPlan worksheet has dinner items listed multiple times. In order to create a unique list for the recipe lookup dropdown list, I used the code found at Extracting a Unique List. [...]

[...] A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula [...]

Actually, there is shorter way without having to do the "array-formula."

The regular formula is: =IF(COUNTIF($A$1:A1,A1)=1,A1,"")

As soon as the formula finds the first one, it will blank-out the dups.

*****Note: Must sort the list first

[...] Copy cell A2 and paste down as far as needed. See this blog post for an explanation: How to extract a unique distinct list from a column [...]

The formula is even faster if you use countifs in Excel 2007 instead of countif. Countifs calculated 7,000 rows with 3 criteria very quickly for me.

Sean,

Great tip, I didn´t know that!

This has been very helpfull, thank you!, going to spend alot of my free time on this site, thanks! :)

=INDEX(List,MATCH(0,if(list2=A1,COUNTIF($B$1:B1,List)),0))

A1="a". Now I want to change the condition to list2=A2 ("b"). Is there a way to move onto the next condition ("b") automatically after it finds all the unique entries for "a"?

A1="A"

A2="B"

Sean,

If order is important, try:

=INDEX(List,IFERROR(MATCH(0,if(list2=$A$1,COUNTIF($B$1:B1,List)),0), MATCH(0,if(list2=$A$2,COUNTIF($B$1:B1,List)),0))

Not important, try:

=INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))

Oscar,

It works great. This saves time in pulling a big list. Countif is so flexible or countifs if using Excel 2007. The only thing now is that I need another column showing if it is an A or B for the unique entries in List.

Dog A

Cat A

Cow B.

Oscar,

I think I figured it out. Just change the index to list2.

=INDEX(list2,MATCH(0,IF(COUNTIF($F$3:$F$4,list2),COUNTIF($F$8:F8,list)),0))

Keep up the great work.

Oscar,

If there is two duplicates with one equal to "A" and the other equal to "B", it only picks up the duplicate once. It should be twice since they have different conditions.

I would like to use =INDEX(List,MATCH(0,if(countif($A$1:$A$2, list2),COUNTIF($B$1:B1,List)),0))

dog a

cat a

dog b

Result

dog A

cat B

How do you fix this?

Sean,

I think you will find the answer in this article: Vlookup with 2 or more lookup criteria and return multiple matches in excel

The original extraction of names from a list works, but on trying the sort order formula, it doesn't. It DOES pick up the 1st alphabetized name, but then just repeats. And yes, the cell ref does increment:

=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)...

=INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A2, List1)...

The full formula is: =INDEX(List1, MATCH(MIN(IF(COUNTIF($A$1:A1, List1)=0, 1, MAX((COUNTIF(List1, "<"&List1)+1)*2))*(COUNTIF(List1, "<"&List1)+1)), COUNTIF(List1, "<"&List1)+1, 0))

I tried sorting the original formula list, and it worked for a moment, and then flipped back. Wild! Formulas in excel I just don't get. I'm an admin, not a coder :) Any help please?

Larry,

I think your formula is copied from here: Unique distinct list from a column sorted A to Z using array formula in excel

How to create an array formula1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

2. Press and hold Ctrl + Shift.

3. Press Enter once.

4. Release all keys.

I have edited the original post, I hope I have explained it in greater detail than before. Read: Unique distinct list from a column sorted A to Z using array formula in excel

The formula doesn't work, is says that a circular reference is created, I downloaded the Excel file with the example and it gives NA as a result.

Dave,

I downloaded the file and it worked here.

Im having the same problem as dave, ive downloaded the sample file and it looks fine when you view it, but as soon as you click into the Formula and press enter it removes the "{" around the statement and it then returns NA. Im using Excel 2007.

Ah! pressing the control + shift after pasting into the formula bar sorts it, what does that actually do ?

That´s how you enter an array formula.

How to create an array formulaCopy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

Press and hold Ctrl + Shift.

Press Enter once.

Release all keys.

I don't know why I can't make this work ...

To be sure I'm not doing anything wrong I made an example like yours but the array formula gives me an error for the first "List" and appears "#NAME?"

I downloaded your example and there is working just fine.

Now I pasted my data in your example and it calculates only for the first 20 rows like in the example. So it isn't working for the whole column ..

Do I need to create a list or something?

pau,

you are right. I forgot to add how to create a named range.

I have now added how to create a named range in this blog post.

Hi Oscar,

This is fantastic.

I tried using this formula and it really helped.

I have a small problem that I am not sure on how to solve.

I now have a list of 15 cells with 3 unique values (arr formula in all 15 cells). When I use this into a pivot table, I want to get the count as 3 since that is the unique count. But I am getting 15 as the value!

I am assuming that the pivot table is reading the formula as a value and counting it. Any way to circumvent that?

ExcelBeginner,

read this post: Count unique distinct values in a pivot table in excel

From your article - https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

You mentioned a formula to make lists from one column in alphabetical order. I've used this formula with success for a couple years but just stumbled on a problem I hadn't encountered before. For some reason it isn't functioning in the attached worksheet with the range I am searching in. You'll see in the attachment that in the Stats sheet D3 I have entered the formula

=IFERROR(INDEX(DlvDT1,MATCH(MIN(IF(COUNTIF($D$2:$D2,DlvDT1)=0,1,MAX((COUNTIF(DlvDT1,"<"&DlvDT1)+1)*2))*(COUNTIF(DlvDT1,"<"&DlvDT1)+1)),IF(ISBLANK(DlvDT1),"",COUNTIF(DlvDT1,"<"&DlvDT1))+1,0)),"")

And it produces a "". The range being looked up is in the Deadheads spreadsheet (Deadheads!$I$2:$I$3000) and named DlvDT1.

I couldn't figure out what was causing it to mess up after a while so I started messing with the range values to see if that had something to do with it. Surprisingly it did. If I do the same formula using range name DlvDTOne (Deadheads!$I$2:$I$706) the results will show, if I make it anything after I706 it will not show.

I also tried to go from Deadheads!$I$639:$I$3000 (DlvDTUno and got it to work successfully but it will not go any lower than I639.

That tells me something in the values between I638 and I707 is causing issues with your formula format. Can you look into it and see what you can figure out? I've made all three tests I explained available in cells D3, D4, & D5 respectively. Let me know if you can figure anything out.

Thanks!

(P.S. - Tried to submit via your upload page and it wouldn't let me. Email me and I can send you the xlsx file.

Hi,

I have a table, I want to find which records that have criteria (example: column TOTAL > 0). PIVOT Table did not solve that I want.

I want relist this table into another sheet.

Could you help me? Thanks.

I was only able to get this to work in Excel2007 by switching to compatibility mode. What is different between the two versions that causes it to fail in Excel2007? Is there a version that works for Excel2007?

Hello,

I'm using VBS with Array

=FilterUniqueSort($A$2:$A$8212)

if there are no data I got #VALUE! how get rid of this please?

Sheet1,

VBA code:

This is actually code that I'm using from this link that is posted up

https://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls

here I got #VALUE! error

p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....

I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show data

Function FilterUniqueSort(rng As Range)

Dim ucoll As New Collection, Value As Variant, temp() As Variant

Dim iRows As Single, i As Single

ReDim temp(0)

On Error Resume Next

For Each Value In rng

If Len(Value) > 0 Then ucoll.Add Value, CStr(Value)

Next Value

On Error GoTo 0

For Each Value In ucoll

temp(UBound(temp)) = Value

ReDim Preserve temp(UBound(temp) + 1)

Next Value

ReDim Preserve temp(UBound(temp) - 1)

iRows = Range(Application.Caller.Address).Rows.Count

SelectionSort temp

For i = UBound(temp) To iRows

ReDim Preserve temp(UBound(temp) + 1)

temp(UBound(temp)) = ""

Next i

FilterUniqueSort = Application.Transpose(temp)

End Function

Function SelectionSort(TempArray As Variant)

Dim MaxVal As Variant

Dim MaxIndex As Integer

Dim i, j As Integer

' Step through the elements in the array starting with the

' last element in the array.

For i = UBound(TempArray) To 0 Step -1

' Set MaxVal to the element in the array and save the

' index of this element as MaxIndex.

MaxVal = TempArray(i)

MaxIndex = i

' Loop through the remaining elements to see if any is

' larger than MaxVal. If it is then set this element

' to be the new MaxVal.

For j = 0 To i

If TempArray(j) > MaxVal Then

MaxVal = TempArray(j)

MaxIndex = j

End If

Next j

' If the index of the largest element is not i, then

' exchange this element with element i.

If MaxIndex < i Then

TempArray(MaxIndex) = TempArray(i)

TempArray(i) = MaxVal

End If

Next i

End Function

thank you for reply

Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one example

Col A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

etc...

also prevous question can this VBS insert or shift cells when new unique data appear?

Thanks

Sheet1,

This is actually code that I'm using from this link that is posted uphttps://cdn.get-digital-help.com/wp-content/uploads/2009/03/Extract-unique-distinct-sorted-values-from-a-cell-range.xls

here I got #VALUE! error

I downloaded the excel file and it works here.p.S. any way that this script shift or insert cell for new unique value that appear and remove when value is not on the list....Yes it does, if the value is in the cell reference.I would like to have 2 lists sorted by this script with unique data under each other but with this one it just take next available cell to show dataThe custom function can only process a single cell reference. I am not sure I understand..Ok I figure out but now I'm getting 0 what shows in line for all arrays in that column and affect formulas from beside cell's that takes value from this one exampleCol A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

etc...

Have you created an array formula with the custom function?Ok I just followed instructions listed abowe and copied VBS so I selected 100 cells and applied array on it..

array is pointed on column on another sheet, but if there are empty cells I got

Col A Col B array Col C

#N/A 0 0

#N/A 0 0

#N/A 0 0

#N/A 0 0

for all of them

How to stop to not shows to the end all 100 same error?

and is there any chance for this VBS instead of Error GoTo 0 put that is empty (doesn't show anythin)? as other formulas aroound when get 0 from col B start to showing #N/A

Thank you

Sheet1,

What is shown in the formula bar? What cell reference are you using?

Did you also copy the selectionsort function from the attached file?

Hi

Many Thanks for your site

I have used "the Extract unique distinct sorted values from a cell range (vba)" code and allmost everything is fine

But the first cell value in the sorted list is allways the first cell value from the input data, not correctly sorted

List1

B

C

D

A

FilterUniqueSort(list1)

B

A

C

D

Is there some way to fix it?

Hej Mats!

I recreated your setup but I can´t see anything wrong?

Hi

This is amazing

I deleted the VBA module and reimported

Now it works :-)

I have had very good use of your excelent code and information about array formulas

Many thanks

Mats

Oscar,

I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection Change AutoFilter on multiple colums. Is there a way to make the list only return the unique values that are visible in the filtered source data?

I see a similar answer above using just an array formula, but my source is too long for that to be practical. Any help would be greatly appreciated.

Robert Jr

Robert JR,

Great question, a blog post is soon coming up.

Robert J,

Read this post: Extract unique distinct values from a filtered table (udf and array formula)

When I do this formula in Excel 2007, it just produces the same list as the original. ??

Terry,

Did you remember to create an array formula? (Ctrl + Shift + Enter)

Did you adjust cell references (bolded)

$B$1:B1,List),0))Hi Oscar, thanks for your reply. Yes I did all that. What I discovered was, this formula only works when the two columns are physically adjacent to each other. I am unable to do this in this case, so my unique list is in another part of the spreadsheet. Seems like it should work though, since I am using a named reference for the source data. I suppose I could just reference the source data in a column next to the unique list as a workaround.

Terry,

They don´t have to be adjacent. They don´t have to be on the same sheet.

Download excel file:Terry.xls

Oscar, I copied my list into your sheet and it behaves the same way as it does in mine. I am using a list of nos. rather than text. Would this matter?

Terry,

I changed the text values to numbers and the formula filters unique distinct numbers.

See attached file:Terry1.xls

I am using the blanks formula below to recover a list when a set of numbers in a second identically sized list(List2) are blank

I was wondering if there is a way to modify this section of the formula so that it searches for a particular colum (Range "B:D") and takes rows 2 to 10 as the list. The column headings will be dates.

=INDEX($A$2:$A$10,MATCH(0,IF(ISBLANK($B$2:$B$10),"",COUNTIF($E$2:E2,$A$2:$A$110)),0))

Does anyone have any insight on this matter?

Cheers

Is there a way to take the results of an Array Formula (i.e. a list of names called CompScenList (a name range)) and gather the unique distinct values? I looked but could not see this question in the history...

Hello Oscar,

It's incredible that you've maintained this post for nearly three years, now I too have a question for you.

I am filtering my list from one sheet to another (Sheet 1 to Sheet 2), and everything seems to be in working order, except that my unique list is printing all my results twice:

Ashley

Ashley

Jenna

Jenna

Angie

Angie

Sarah

Sarah

Here is a copy of the formula I am using:

=INDEX(ClientList,MATCH(0,COUNTIF($A$1:A1,ClientList),0)) + Ctrl + Shift + Enter

What can I do to solve this?

Next, I am already using the first row on Sheet 1, and the first two rows on Sheet 2 to for column titles. When I attempt to change "COUNTIF($A$1:A1..." to "COUNTIF($A$3:A3..." I get a circular reference error. What should I do to make sure my unique list isn't checking for column titles?

Thank you in advance for your time!

-Philip

Please help to create a formula to pick all cells refering to a pertical condition.

Let me brief, suppose Colume A is having a list of employees (ofcourse, unique values) and Column B is having thier respective higher qualification (consider some employees with same qualification), now I want to put formula which enables to find list of all employees if I type a qualification (side by side) i.e if I type "MS" in Cell C1, the list of all employees, who are with "MS" in column B - should be there in D1, E1, F1........

Raja,

You can do exactly that with the formula in this post:

How to return multiple values using vlookup

Thanks for commenting!

Philip Adams,

If you enter the array formula in cell B3, the relative cell reference (bolded) in the formula must be:

$B$2:B2,ClientList), 0))When you copy the formula down to cell B4 the relative cell reference changes automatically to:

$B$2:B3,ClientList), 0))If you use the same cell reference as the current cell, you will get a circular reference warning.

Oscar,

Thank you very much for your speedy reply. I was wondering if it would be possible to add more criteria to the unique list. For example, if I only wanted the names in ClientList to appear if the text "Auto-Renewal" appeared in their respective column O.

Thank you again for your time.

-Philip

Philip Adams,

This post is probably interesting: Create unique distinct list from column where an adjacent column meets criteria

Hey Paul,

Trying to do something very easy, I'm sure. :)

I have a list that shows people who have been interviewed and whether they accepted or not. For the ones who have not, I want to have another table that lists their names and then I can comment on reasons for not accepting.

So I want a table like this:

Person1 - accepted

Person2 - not accepted

Person3 - accepted

Person4 - not accepted

To output to a table like this:

Person2

Person4

... and so on, in order...

Thanks!!

Q:

Considering A1: name

B1: Status

A2 to A10 person1, person2...

B2 to B10 accepted, not accepted...

in D1 accepted

in E1 not accepted

then in d2:

=IFERROR(INDEX($A$2:$A$10, SMALL(IF("accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(A1))), "")+Ctrl+Shift+Enter

and in e2:

=IFERROR(INDEX($A$2:$A$10, SMALL(IF("not accepted"=$B$2:$B$10, MATCH(ROW($B$2:$B$10), ROW($B$2:$B$10)), ""), ROW(B1))), "")+Ctrl+Shift+Enter

With this you'll have the list of the accepted people in column D and the list of not accepted in column E.

file available at:

https://uploading.com/files/e76mam54/Q.xlsx/

Cheers,

Cyril.

You're awesome Cyril! It totally worked... thank you so much!

Thank you so much for the unique values formula. It saved tons of coding to replicate and it does it automatically.

Dear Mr. Oscar,

Excellent, thanks for spending your valuable time for me.

Really its amazing, i can't believe that alla these things can happend in excel, after reading these posts.

But in the answer you provided for me, some formulae are used, which were new to me. Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.

Thanks in advance

Chip,

Thanks, but the work is done by Eero.

Raja,

Ofcourse I know that Excel help is there, but is there any thing else (any blog or page or book) that explains more effieintly that help.I am sorry, I don´t know of any books about array formula basics. Maybe someone else can help me out here?

Cyril,

thanks for helping me out!

Oscar,

Why thank you!

your website is a gold mine, not only it is user friendly but it is filled with examples and real life cases, frankly i think you have here the best excel oriented forum and learning site.

Glad I could be of any assistance, but as I said, you are still the reference here.

kudos!

Raja,

Excel 2007 Bible by John Walkenbach

Publication Date: January 3, 2007 | ISBN-10: 0470044039 | ISBN-13: 978-0470044032

Excel 2010 Bible by John Walkenbach

Publication Date: May 10, 2010 | ISBN-10: 0470474874 | ISBN-13: 978-0470474877 | Edition: 1

Excel 2007 Formulas by John Walkenbach

Publication Date: February 12, 2007 | ISBN-10: 0470044020 | ISBN-13: 978-0470044025

Excel 2010 Formulas by John Walkenbach

Publication Date: May 24, 2010 | ISBN-10: 0470475366 | ISBN-13: 978-0470475362 | Edition: 1

Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques by Mike Smart

Publication Date: September 11, 2009 | ISBN-10: 0955459931 | ISBN-13: 978-0955459931 | Edition: 1

Microsoft® Office Excel® 2007: Data Analysis and Business Modeling by Wayne L. Winston

Publication Date: May 16, 2007 | ISBN-10: 0735623961 | ISBN-13: 978-0735623965

Microsoft Excel 2010 In Depth by Bill Jelen

Publication Date: July 5, 2010 | ISBN-10: 0789743086 | ISBN-13: 978-0789743084 | Edition: 1

Cheers

Congratulations for your job, Oscar.

How I can modify the formula to get to make disappear B2(DAVID) because the A1 and D2 have the same value

1/ A B D

2/ 2 DAVID 2

3/ 5 PETER 3

The formula that I use right now is this one:

[=SI.ERROR(INDICE(Devos!$A$2:$A$9999;COINCIDIR(0;CONTAR.SI($B$1:B1;Devos!$A$2:$A$9999);0));"")]

Thanks in advance

(Sorry,this way is understand better)

Congratulations for your job, Oscar.

How I can modify the formula to get to make disappear B2(DAVID) because the A2 and D2 have the same value

1/---A-----B-----D

2/---2---DAVID---2

3/---5---PETER---3

The formula that I use right now is this one:

[=IFERROR(INDEX($A$2:$A$9999;MATCH(0;COUNTIF($B$1:B1;$A$2:$A$9999);0));"")]

Thanks in advance

Jan,

See attached file:

Jan.xlsx

Firstly, great resource, fantastic work Oscar.

Quick question I have three lists of names in the same sheet and I need to pull them into three different distinct lists. I assume All i need to do is reference the correct fields E.G.

=INDEX(Primary,MATCH(0,IF(ISBLANK(Primary),"",COUNTIF($Q$1:Q1,Primary)),0))

=INDEX(Secondary,MATCH(0,IF(ISBLANK(Secondary),"",COUNTIF($Q$12:Q12,Secondary)),0))

The Primary list returns ok, the Seconday list retuns 0.

I can work with the Primary list for now as I'm trying to lookup a beginging date and end date based on the distinc list but am I doning something wrong with my secondary list?

Nick,

$Q$12:Q12, Secondary)), 0))You have to enter this array formula in cell Q13 for this to work. You also have to copy the cell Q13 (not the formula) and paste it down as far as needed. Make sure your named range

Secondaryis correct.Hi; Thank you for this article. Trying to better my Excel skill, and this topic is one I have been trying to get down for awhile.

I've downloaded your file, and at the very early stage, I am getting #N/A:

=COUNTIF($B$1:B1,List) returns a value of "0" as it is supposed to. But when I proceed to the next step, which is

=MATCH(0,COUNTIF($B1:B1,List),0)returns #N/A and I cant figure out why. Maybe it is a rookie error?

Thank you for you help!

Versch,

Array formula:

=MATCH(0,COUNTIF($B1:B1,List),0)

1. Select cell B2

2. Paste formula

3. Press and hold Ctrl + Shift

4. Press Enter

Hi Oscar,

Thank you for the update. You are correct, I had not made B2 into an array formula, which I now did as per your instruction. However, my

=MATCH(0,COUNTIF($B1:B1,List),0)

returns #N/A again.

Thank you; I will try re-engineering this till it works right.

I had a two coloumn, one coloumn have fifteen records and another have 100 records. both coloumn does't have the same records. i want to merge the both coloumn means i want to add that fifteen records against of against of 100 records (100*15) but i am not able to do that. For e.x. Column A-15 Unique Records and Column B has 100 Unique records and both are not matchning. I want to merge the both records (100*15) and make a single records in every row without missing any single records. Please suggest how to do that.

Aman Kr Tadneja,

Perhaps you are looking for this post:

Extract a unique distinct list from two columns using excel 2007 array formula

Hello Oscar,

Just wanted to leave a thank you note for the countless number of times your formulae have helped me get unique lists faster. I have been through all the "unique-list" posts on your site and have found them to be very useful. Thanks and keep up the good work!!

Playercharlie,

thanks for commenting!

The issue with #N/A is if you're using a named list that concludes a whole column. I had the same issue and could not figure out what iw as doing wrong as I kept getting #N/A or zero no matter what I tried.