## 5 easy ways to extract unique distinct values

*Article updated on May 02, 2018*

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. Not only an exceptionally small regular formula, if you want to use that, but also awesome built-in features in Excel that makes your work so much easier.

First, let me explain the difference between **unique** values and **unique distinct** values, it is important you know the difference so you can find the information you are looking for on this web page.

The picture above shows a list of values in column B, note value AA has a duplicate. **Unique distinct values** are all cell values but duplicate values are merged into one **distinct** value.

To filter duplicates, read this post: Extract a list of duplicates from a column

Column F contains **unique** values, meaning values that exist only once in column B. Value AA is not in column F because it has a duplicate, in other words, AA is not unique in column B.

### What is the easiest way to filter unique or unique distinct values for my scenario?

I would choose the advanced filter if you are not looking for a formula. It lets you quickly filter a unique distinct list.

If you know that you will be extracting unique distinct values from time to time, like in a dashboard or an interactive worksheet, I 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.

Conditional Formatting allows you to format cells determined by a built-in rule or a formula you construct. In this post, you will find a Conditional Formatting formula that highlights unique and unique distinct values.

The Excel 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. You will be surprised how easy it is to start working with Excel Pivot tables.

There is also a useful array formula in this article that extracts a case-sensitive unique distinct list.

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

**Working with unique distinct values**- How to extract unique distinct values from a column [Formula]
- Extract unique distinct values (case sensitive) [Formula]
- Filter unique distinct values [Advanced Filter]
- Highlight unique distinct values [Conditional Formatting]
- Hide duplicate values [Conditional Formatting]
- Extract unique distinct sorted values from a cell range [UDF]
- Extract unique distinct values [Macro]
- Build a list of unique distinct values [Pivot Table]
- Filter unique distinct values and records from multiple sheets [Add-In]
- How to extract unique distinct values from a column [Array Formula]

**Working with unique values****Tips and tricks**- Useful tips
- The formula is too slow, can I make it faster?
- Named ranges
- Excel defined tables
- Remove errors, Excel version 2007 and later
- Remove errors, Excel version 2003 and earlier
- Blank cells in range
- Useful links

### Create a list of unique distinct values [Formula]

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

**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.*

**The formula in cell D3:**

The following video shows you how to implement and use the formula above:

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

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

The following post demonstrates how to extract a unique distinct list sorted from A to Z and removing blank cells:

Create a unique distinct sorted list containing both numbers text removing blanks

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

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

Vlookup – Return multiple unique distinct values

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

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

Unique distinct list sorted alphabetically based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Filter unique distinct values from two different columns:

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 […]

Filter unique distinct values from three columns:

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

How to extract unique distinct records:

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

#### Copy unique distinct values

To copy unique distinct values to another location you must make sure you copy the values and not the formula:

- Select list
- Copy list, shortcut keys: CTRL + C or press this button:
- Right click on destination cell and click on the black arrow next to "Paste Special..."

- Then click on "Paste Values" button

#### Explaining formula in cell D3

**Step 1 - Count previous values above current cell**

The COUNTIF function allows you to count values using a condition. With the help from an expanding cell reference, the formula knows which of the values that have been extracted.

In cell D3 no values have been extracted so it compares the value in the cell above current cell, this happens to be the Header value. Make sure you don't have a value in the list that matches the header value, it won't be extracted.

COUNTIF($D$2:D2,$B$3:$B$21) is entered in column F, displayed in the picture below.

The value in cell D2 is not found in any instance in cell range B3:B21, all values in the array are 0 (zero). Note that the array has the same size as the list in column B, 19 values.

**Step 2 - Compare array with 0 (zero)**

To identify values that have not been shown the formula compares the array with 0 (zero) and the result are boolean values (TRUE or FALSE) for each value in the array.

COUNTIF($D$2:D2,$B$3:$B$21) = 0

The array contains 19 boolean values, all TRUE.

**Step 3 - Divide 1 with array**

The boolean value TRUE is equal to 1 and FALSE is equal to 0. If a value in the array is TRUE the result will be 1 because 1/TRUE equals 1.

If a value in the array is FALSE the result will be #DIV0! because 1/FALSE is 1/0 and you can't divide a number with zero. Excel returns an error.

The good thing about the LOOKUP function is that it ignores errors, see next step.

**Step 4 - LOOKUP value**

The LOOKUP function is designed to work with sorted cell ranges or arrays, you get weird results if they are not sorted. Be careful using the LOOKUP function.

However, in this case, the values in the array are either 1 or #DIV0!. Surprisingly it ignores errors, the only thing it can find then is a value that is 1.

The first argument in the LOOKUP function is 2 so the function finds the last largest value that is equal to 2 or smaller.

LOOKUP(2,1/(COUNTIF($D$2:D2,$B$3:$B$21)=0),$B$3:$B$21)

becomes

LOOKUP(2,{1;1;1;1;1;1;1;1;1; 1;1;1;1;1;1;1;1;1;1},$B$3:$B$21) and matches the last value in the array. LOOKUP function then returns the corresponding value in cell range $B$3:$B$21 which is Almagro, Nicolas

#### Download excel *.xlsx file

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

#### Excel 2007 users (and later versions) 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 disadvantage, 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 D2:**

*and copy it down as far as necessary.*

Recommended reading:

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

#### 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 ignore 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 in column D.

If you want a header name you can use the slightly larger formula, displayed in column F below.

**Update 2017-09-01, smaller regular formula in cell D3:**

**Formula in cell F3 is slightly larger than formula above:**

#### Watch a video where I explain how these two formulas work

This article shows you how to fill blank cells with values or formulas

How to automatically fill all blanks with missing data or formula

Question: I have two lists. The first list contains two columns, unique values and names. The second list contains unique […]

Learn how to extract non-blank cells in a list using a formula:

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

#### Download excel *.xlsx file

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

### Extract/filter unique distinct values [Advanced Filter]

First a little reminder, unique distinct values are all cell values but duplicate values are merged into one distinct value.

The following video shows you how to filter unique distinct values using Advanced Filter:

#### Instructions - Copy unique distinct values to another location

- 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 unique distinct values
- Click "Copy to: and select a range
- Click "Unique records only"
- Click "OK"!

#### Instructions - Filter unique distinct values, in place

If you choose to filter unique distinct values, click on the first option button in the dialog box.

You can then select unique distinct values and paste to another location.

The picture below shows you the selected distinct values after I cleared the Advanced Filter.

#### Learn more about other powerful excel features

An Advanced Filter is not the only powerful built-in feature in Excel, I highly recommend that you learn pivot tables. Perhaps the most powerful tool but also the least known:

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

The Excel defined table is also extremely useful, it allows you to quickly sort, filter and manipulate data. Learn that and much more:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

### Highlight unique distinct values [Conditional Formatting]

The following image shows you unique distinct values highlighted using Conditional Formatting.

This video demonstrates how to highlight unique distinct values:

#### Instructions

- Select cell range B3:B21
- Go to tab "Home" on the ribbon
- Click on "Conditional Formatting" button

- Click on "New Rule..."

- Click on "Use a formula to determine which cells to format:"
- Type this formula: =COUNTIF($B$3:B3,B3)=1
- Click on "Format..." button
- Pick a color
- Click OK button
- Click OK button again

#### Explaining Conditional Formatting formula

A CF formula works somewhat differently than a regular formula, it is possible that you can't even see if a cell range has CF applied to it or not, unless you select the cell range and click on the CF button and then click on "Manage Rules...".

The COUNTIF function has two arguments, the first argument is the cell range you want to count a specific value in. The second argument is the value you want to count.

The first argument uses both relative and absolute cell references, $B$3:B3. The absolute part has dollar signs $B$3 meaning it does not change when the Conditional Formatting formula is applied to the next cell.

The relative part B3 does change when the Conditional Formatting formula is applied to the next cell.

In cell B3 the function is COUNTIF($B$3:B3,B3) and in cell B4: COUNTIF($B$3:B4,B4) and so on. This technique lets you highlight the first instance of a value but not duplicate values.

How do we know if the value is a unique distinct value? Compare COUNTIF($B$3:B3,B3) to 1 and it will return TRUE or FALSE, like this: COUNTIF($B$3:B3,B3)=1

**Tip!** Right-click on a highlighted cell, click on Sort and then on "Put Selected Cell Color On top" to arrange unique distinct values at the very top of your list.

The picture below shows you all unique distinct values sorted together.

### 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 D3:**

This video demonstrates how to build a formula that extracts a case-sensitive unique distinct list:

This post shows you how to extract a case sensitive unique list from a column:

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

#### Explaining the array formula in cell C3

**Step 1 - Transpose previous values**

TRANSPOSE($D$2:D2)

becomes

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

and returns

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

Note that the ; (semicolon) changes to a , (comma)

Recommended reading:

How to use the TRANSPOSE function

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

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

EXACT($B$3:$B$15, TRANSPOSE($D$2:D2))

becomes

EXACT($B$3:$B$15, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT($B$3:$B$15, 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($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))

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}

Recommended article:

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

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

FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},MATCH(ROW($B$3:$B$15),ROW($B$3:$B$15)))

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 $B$3:$B$15

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

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

MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))), 0)

becomes

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

and returns 2.

Identify the position of a value in an array.

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

INDEX($B$3:$B$15, MATCH(0, FREQUENCY(IF(EXACT($B$3:$B$15, TRANSPOSE($C$1:C1)), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""), MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15))), 0))

becomes

INDEX($B$3:$B$15, 2)

and returns "CC" in cell C3.

Gets a value in a specific cell range based on a row and column number.

**Download excel *.xlsx file **

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

#### Articles with topic: Case sensitive

Learn to do case sensitive lookups and return multiple values:

Case sensitive lookup and return multiple values

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

The following article demonstrates a User Defined Function that filters unique distinct values based on case sensivity:

Filter unique distinct values (case sensitive) [Excel UDF]

The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]

Filter unique distinct records (case sensitive)

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]

Learn to construct a formula that counts case sensitive unique distinct values:

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

This article shows you how to count multiple text strings in a cell range (case-sensitive)

Count multiple text strings in a cell range

The array formula below counts how many times multiple text strings exist in a cell range. It is easy to […]

### Extract unique distinct sorted values from a cell range [UDF]

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:**

#### This video explains how to implement and use the User Defined Function

**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

Recommended reading

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

**VBA code**

I am using the selection sort 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

Function SelectionSort(TempArray As Variant) Dim MaxVal As Variant Dim MaxIndex As Integer Dim i, j As Integer For i = UBound(TempArray) To 0 Step -1 MaxVal = TempArray(i) MaxIndex = i For j = 0 To i If TempArray(j) > MaxVal Then MaxVal = TempArray(j) MaxIndex = j End If Next j If MaxIndex < i Then TempArray(MaxIndex) = TempArray(i) TempArray(i) = MaxVal End If Next i 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 User Defined Functions

Filter unique distinct records (case sensitive)

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

### 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.

#### Watch this video where I demonstrate the Excel Add-In

#### 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 a unconditional 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:**

### Create a list of unique distinct values [Array Formula]

I recommend using the regular formula above since it is smaller and has an advantage of not being an array formula.

**Array formula in cell D3:**

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

The formulas above has an issue with blank cells, it returns a 0 (zero) in your list. This article shows you how to ignore 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 several […]

#### How to create an array formula

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

- Copy the array 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($B$3:$B$21, MATCH(0, $D$2:D2, $B$3:$B$21), 0))}

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

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

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

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

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:

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**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:

Identify the position of a value in an array.

**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:

Gets a value in a specific cell range based on a row and column number.

**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:

How to use absolute and relative references

What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

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

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

Column D in the picture below filters all unique values from column B. Unique values are values that exist only once in column B.

Example, Roger, Federer is not in column D because there is more than one value of this name 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.

**Update 2017-08-30 **

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

**Formula in cell D3:**

This post shows you how to extract a case sensitive unique list from a column:

How to extract a case sensitive unique list from a column

My definition of unique values are values that exist only once in a cell range. The image below shows you […]

Learn to create a list of unique values extracted from a cell range:

Extract unique values from a range

Question: How do I extract values only occurring once in a range? Answer: A range (tbl_text) containing text values Array […]

The following article shows you how to filter unique values sorted alpabetically:

Filter unique values sorted from A to Z

Introduction A unique value is a value that only exist once in a list. A unique distinct list contains all cell […]

Recommended article

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

**Array formula in D3:**

#### Explaining array formula in cell D3

**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.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**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:

How to use absolute and relative references

What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

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.

Identify the position of a value in an array.

**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.

Gets a value in a specific cell range based on a row and column number.

#### Download excel *.xlsx file

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

To extract duplicates, see this post:

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

### Highlight unique values [Conditional Formatting]

The following video shows you how to color unique values using conditional formatting. Remember, it highlights only unique values, in other words values that exist only once in the list.

#### Instructions

- Go to tab "Home" on the ribbon

- Click "Conditional Formatting" button
- Hover over "Highlight Cell Rules"
- Click on "Duplicate Values..."

- Click on the leftmost drop-down list and change it to "Unique"
- Pick a formatting if you like
- Click OK button

The picture above shows you, for example, that the first name in the list has a duplicate so that name is not highlighted in any cell.

**Tip! **Did you know that you can put highlighted values to the top

#### 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 of occurrence using array formula in excel
- Create a unique list and sort by occurrences 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

Create a unique distinct list using Advanced Filter in a macro [VBA]

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]Automate net asset value (NAV) calculation on your stock portfolio

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
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.