How to extract a case sensitive unique list from a column
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive).
Table of Contents
My definition of unique values is 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 exist 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:
Recommended articles
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
Make sure you read the following article if you want to extract duplicate values:
Recommended articles
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
1.1 How to enter an array formula
- Copy (Ctrl + c) above formula.
- Double press with left mouse button on cell C2.
- Paste (Ctrl + v) to cell C2.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.
Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
1.2 Explaining array formula in cell D3:
You can easily follow along if you get the attached file and select cell D3. Then go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button.
Press with left mouse button on "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.
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.
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; "";""}
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; "";""},1) and returns 2.
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) returns "CC" in cell D3.
2. Extract a case sensitive unique list from a column - Excel 365
Formula in cell D3:
The following formula is for Excel 365 users:
2.1 Explaining formula
Step 1 - Convert a vertical array to horizontal
The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa. This works also with arrays.
TRANSPOSE(B3:B15)
returns {"Aa", "CC", ... , "BB"}
Step 2 - Check if values match (case sensitive)
The EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper and lower case letters.
EXACT(B3:B15, TRANSPOSE(B3:B15))
returns the following array displayed in the image below.
The image above shows the array in cell range C3:O15. It compares each value against each other and if there are two or more TRUE the value has a duplicate.
Step 3 - Multiply with 1
The MMULT function can't work with boolean values (TRUE or FALSE) , however, there is a workaround. They have numerical equivalents that work with the MMULT function.
TRUE = 1
FALSE = 0 (zero)
EXACT(B3:B15, TRANSPOSE(B3:B15))*1
returns the following array displayed in the image below.
Step 4 - Create a sequence of 1
The ROW function returns a number representing a row number based on a cell reference. If the cell reference points to multiple cells an array of numbers is returned.
ROW(B3:B15)^0
becomes
{3; 4; ... ; 15}^0
and returns {1; ... ; 1}.
Step 5 - Sum values row-wise
The MMULT function calculates the matrix product of two arrays, this can be used to sum values either column-wise or row-wise, the result is an array.
MMULT(EXACT(B3:B15, TRANSPOSE(B3:B15))*1, ROW(B3:B15)^0)
returns the following array displayed in the image below in column P.
The image above shows the array in column P.
Step 6 - Check if unique
Values that return 1 exist only once, two or more means duplicates.
MMULT(EXACT(B3:B15, TRANSPOSE(B3:B15))*1, ROW(B3:B15)^0)=1
and returns {FALSE; TRUE; ... ; FALSE}.
Step 7 - Filter unique values
The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(B3:B15, MMULT(EXACT(B3:B15, TRANSPOSE(B3:B15))*1, ROW(B3:B15)^0)=1)
returns {"CC"; "bb"; "aa"; "Cc"}.
Step 8 - Shorten formula
The LET function assigns names to calculation results. This can shorten formulas considerably and make them run much faster.
LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])
LET(z, B3:B15, FILTER(z, MMULT(EXACT(z, TRANSPOSE(z))*1, ROW(z)^0)=1))
4. Filter unique distinct values - case sensitive - UDF
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters.
There are now Excel 365 formulas that creates this task:
- Filter unique distinct rows case sensitive - Excel 365 recursive LAMBDA function
- Count unique distinct rows case sensitive
A User defined Function in Excel is a custom function that anyone can use, simply copy the code to your workbook and you are good to go, see details below.
Array formula in cell D3:D10:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Have you read the article that extracts unique distinct values (case sensitive) using an array formula?
User defined Function Syntax
CSUnique(rng)
Arguments
Parameter | Text |
rng | Required. The range you want to use. |
VBA
'Name function and argument Function CSUnique(rng As Range) 'Declare variables and data types Dim cell As Range, temp() As String, i As Single, iRows As Integer 'Redimension array variable so it can grow using Redim Preserve statement ReDim temp(0) 'Iterate through each cell in range For Each cell In rng 'Iterate through values in array variable temp For i = LBound(temp) To UBound(temp) 'If value is equal to cell value If temp(i) = cell Then 'Add one to variable i i = i + 1 'Stop For ... Next statement Exit For End If Next i 'Subtract variable i with 1 i = i - 1 'If value in array variable temp is not equal to cell value If temp(i) <> cell Then 'Save cell value to array variable temp temp(UBound(temp)) = cell 'Add another container to array variable temp ReDim Preserve temp(UBound(temp) + 1) End If Next cell 'Count how many cells have been used when entering UDF iRows = Range(Application.Caller.Address).Rows.Count 'To prevent error value the UDF adds blanks to remaining containers If iRows < UBound(temp) Then temp(iRows - 1) = "More values.." Else For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i End If 'Return array variable temp to worksheet CSUnique = Application.Transpose(temp) End Function End Function
Where to copy vba code?
- Press Alt-F11 to open visual basic editor
- Press with right mouse button on on your workbook in 'Project Explorer' window
- Press with left mouse button on 'Insert'
- Press with left mouse button on 'Module'
- Copy above VBA code
- Paste VBA code to the code module
- Exit visual basic editor
Unique values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
What's on this page Extract unique values from two columns - Excel 365 Extract unique values from two columns - […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Excel categories
4 Responses to “How to extract a case sensitive unique list from a column”
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
[…] Excel udf: Filter unique distinct values (case sensitive) […]
I am trying to count the number of unique strings of characters separated by deliminator in a single cell. The strings are made up of either several numbers, combined numbers and text, or single digit numbers. I need to count:
1- Unique strings of numbers only
2- total sets of strings in a cell
3- total number of single digits
4- total number of unique single digits in a cell
5- total number of words in a cell
6- total number of unique words in a cell (specific words)
Can anyone help me? I've been trying to find answers to this for days now. I was able to find a way to count unique words, but for some reason it doesn't always work. Right now, I'm pressed to find out how to count the unique serial numbers in a cell.
I'm looking to get a count of unique strings of numbers or numbers and text within a single cell, each separated by the vertical bar.
I have searched for days and cannot find anyone that can help me. Surely, you must know how to do this! Please!
MARGIE CHAPPELL
I recommend that you check out the "Text to Columns" feature, it will separate values in a cell separated by a delimiting character into multiple cells.
https://www.laptopmag.com/articles/use-text-columns-excel
Once you have values separated you can use the UDFs I have on my website.