Filter unique distinct records (case sensitive) [UDF]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record containing A and B is not the same as a record containing a and b.
A User defined Function is a custom function anyone can build, you simply add the VBA code to your workbook and you are good to go.
Array formula in cell F3:G6:
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.
User defined Function Syntax
UniqueRecords(rng)
Arguments
Parameter | Text |
rng | Required. The range you want to use. |
Where to copy VBA code?
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste above vba code
- Exit visual basic editor
Excel user defined function:
'Name function and declare argument Function UniqueRecords(rng As Variant) As Variant() ' This udf filters unique distinct records (case sensitive) 'Declare variables and data types Dim r As Single, c As Single, temp() As Variant, k As Single Dim rt As Single, ct As Single, a As Single, b As Single Dim i As Single, j As Integer, iCols As Single, iRows As Single 'Save values in cell range to array variable rng (yes, same name) rng = rng.Value 'Change size of array variable rng ReDim temp(UBound(rng, 2) - 1, 0) 'Iterate through rows in array variable rng For r = 1 To UBound(rng, 1) 'Iterate through rows in array variable temp For rt = LBound(temp, 2) To UBound(temp, 2) 'Iterate through columns in array variable rng For c = 1 To UBound(rng, 2) ' If temp value is equal rng value then increment variable a with 1 If temp(c - 1, rt) = rng(r, c) Then a = a + 1 Next c 'If a is equal to the number of columns in rng then all values in record match If a = UBound(rng, 2) Then a = 0 b = 0 Exit For Else a = 0 b = b + 1 End If Next rt If b = UBound(temp, 2) + 1 Then For c = 1 To UBound(rng, 2) temp(c - 1, UBound(temp, 2)) = rng(r, c) Next c ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) b = 0 End If Next r k = Range(Application.Caller.Address).Rows.Count If Range(Application.Caller.Address).Columns.Count < UBound(rng, 2) Then MsgBox "There are more columns, extend user defined function to the right" End If If k < UBound(temp, 2) Then MsgBox "There are more rows, extend user defined function down" Else For i = UBound(temp, 2) To k ReDim Preserve temp(UBound(temp, 1), i) For j = 0 To UBound(temp, 1) temp(j, i) = "" Next j Next i End If UniqueRecords = Application.Transpose(temp) End Function
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 in the above picture extracts unique distinct values also considering lower and upper case letters. […]
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 […]
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 […]
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.