## 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 a list in column B, some of these values have duplicates.

The list in column D contains only values that are unique, Aa and BB exists only once in the list, all other values have a duplicate.

The formula extracts CC, bb, aa and Cc because they exist only once in column B.

**Array formula in cell D3:**

If you are looking for a unique distinct list, read this post: Extract unique distinct values (case sensitive) [Formula].

This post explains how to do a case-sensitive VLOOKUP and returning multiple values:

**Search case sensitive and return multiple values**

Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values

Make sure you read the following article if you want to extract duplicate values:

**Extract a list of duplicates from a column**

Comments(41) Filed in category: Duplicate values, Excel

### How to enter an array formula

- Copy (Ctrl + c) above formula
- Double click on cell C2
- Paste (Ctrl + v) to cell C2
- Press and hold CTRL + SHIFT simulatneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

### Explaining array formula in cell D3:

You can easily follow along if you download the attached file and select cell D3. Then go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Click "Evaluate" button shown in above picture to move to next step.

#### Step 1 - Check if values are case sensitive

The EXACT function is case sensitive function that allows you to compare values. If they match EXACT returns TRUE, if not FALSE.

EXACT($B$3:$B$15,TRANSPOSE(B$3:$B$15))*1

If we use TRANSPOSE we can compare values against each other to build an array, in a single calculation. The following picture shows you this array as an index table, I have highlighted cells that match green. Example, cell C3 shows you the result of a comparison between the value in cell C2 and B3. Since it is the same value they must match and the formula returns 1 and is highlighted green.

It is now obvious that value Aa has a duplicate because cell J3 is also highlighted green.

Incredible that Excel allows you to do such a complicated calculation in a single cell.

Recommended reading

Comments(4) Filed in category: Excel, Transpose

#### Step 2 - MMULT function lets you SUM values column-wise or row-wise

The MMULT function is like the SUMPRODUCT function but on steroids, let me explain. SUMPRODUCT lets you multiply and then sum values, the result is a single value.

MMULT lets you multiply and sum values either column-wise or row-wise, the result is an array.

MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0) is entered in column P, see picture below.

It is now easy to spot unique values in the index table, if column P contains 1 the corresponding value in column B must be unique.

Recommended reading

**MMULT function – Matrix multiplication**

Comments(4) Filed in category: Excel, MMULT function

#### Step 3 - Check if value in array is equal to 1 and if so return corresponding row number

Column Q shows you corresponding relative row number if value in column P is equal to 1.

IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), "")

returns this array: {"";2;"";""; "";"";7; "";9;"";11; "";""}

Recommended reading

Comments(9) Filed in category: Excel

#### Step 4 - Filter the k-th smallest row number

SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},1)

and returns 2.

Recommended reading

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel

#### Step 5 - Return value based on coordinate

INDEX($B$3:$B$15, SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1)))

becomes

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

becomes

INDEX({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"; "BB"}, 2)

and returns "CC" in cell D3.

Recommended reading

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

### Download excel *.xlsx file

### Category: Unique values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

Filter unique rows and sort by date using array formula in excel

Question: How do I filter unique rows and sort by date? Answer: Column A and B are the original list. […]Comments(10) Filed in category: Excel, Unique values

Create unique list from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]Comments(10) Filed in category: Excel, Unique values

List all the unique events for a month in excel (array formula)

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]Comments(5) Filed in category: Dates, Excel, Unique distinct values, Unique values

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]Comments(4) Filed in category: Duplicate values, Excel, Unique values

Comments(4) Filed in category: Excel, Unique values

Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel […]Comments(2) Filed in category: Excel, Unique distinct values, Unique values

Extract unique values from a range using array formula in excel

Question: How do I extract values only occuring once in a range? Answer: A range (tbl_text) containing text values Array […]Comments(2) Filed in category: Excel, Unique values

Filter unique values from a range using array formula in excel

Unique values in a range are values occurring only once. This is what I am going to do in this […]Comments(2) Filed in category: Excel, Unique values