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

### Download Excel file

Enter your email to receive the workbook.Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

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

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

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

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

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 comma separated values [UDF]

I received an email from one of my sevenÂ blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Count cells by cell and font color

Dave asks: How difficult would it be to make it count colour alone (so not unique values) and / or […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form