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

The following formula is for Excel 365 users:

Check out the formula below if you use an earlier Excel version.

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:

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

Case sensitive lookup 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

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

Extract a list of duplicates from a column

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

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

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

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

How to use the TRANSPOSE function

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

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and […]

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

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

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

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

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

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

### Download excel *.xlsx file

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

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 EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

Filter unique values and sort based on adjacent date

The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

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

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form