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

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

List files in a folder and subfolders [UDF]

This article demonstrates a user definedÂ function that lists files in a ggiven folder and subfolders. A user defined function is […]

Search for a file in folder and subfolders [UDF]

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Count unique distinct values by cell color

This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]

Substitute multiple text strings [UDF]

The SUBSTITUTE and REPLACE functions can only handle one string,Â the following User-Defined Function (UDF) allows you to substitute multiple […]

This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

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