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.
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
User defined function category
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one […]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article demonstrates a user defined function that extracts duplicate values and also count duplicates. Example, the image below shows a list containing […]
This post describes a custom function (User defined Function) that extract values existing only in one out of two cell […]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Functions in this article
More than 600 Excel formulas
Excel categories
4 Responses to “Filter unique distinct values (case sensitive) [UDF]”
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.
[…] 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.