## 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 and the other formula for earlier Excel versions.

Oscar,

I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection Change AutoFilter on multiple colums. Is there a way to make the list only return the unique values that are visible in the filtered source data?

I see a similar answer above using just an array formula, but my source is too long for that to be practical. Any help would be greatly appreciated.

Robert Jr

### Update 1/10/2021 - dynamic array formula

Formula in cell B26:

The formula above contains four new functions available to Excel 365 subscribers:

UNIQUE, FILTER, SEQUENCE and LET functions. Dynamic array formulas and spilled array behavior

### Array formula

I modified a formula by Laurent Longre found here: Excel Experts E-letter from John Walkenbach's web site.

Array Formula in cell B26:

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.

### Explaining array formula in cell B26

#### Step 1 - Create array

This step is necessary in order to be able to use the SUBTOTAL function in the next step. The ROW function returns row numbers based on a cell reference.

The MATCH function converts the row number array to an array that starts with 1.

OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)

becomes

OFFSET(Table2[First Name], MATCH({2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}, {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})-1, 0, 1)

becomes

OFFSET(Table2[First Name], {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}-1, 0, 1)

becomes

OFFSET(Table2[First Name], {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

becomes

OFFSET({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

The OFFSET function creates an array of arrays that the SUBTOTAL function can process.

OFFSET({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

and returns

{ {"Kaya"}; {"Fraser"}; {"Jui"}; {"Cyril"}; {"Spencer"}; {"Horace"}; {"Emmit"}; {"Lynna"}; {"Charley"}; {"Fraser"}; {"Mckinley"}; {"Tiara"}; {"Damien"}; {"Linnie"}; {"Jui"}; {"Fraser"}; {"Siena"}; {"Lynna"}; {"Kelton"}; {"Kaya"} }

#### Step 2 - Which values are hidden?

The SUBTOTAL function will in this step count each array in the array as one and return 1 if the value is nonempty and is not visible. The first argument is 3 representing COUNTA function. It counts the number of cells that are not empty.

SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1))

becomes

SUBTOTAL(3, { {"Kaya"}; {"Fraser"}; {"Jui"}; {"Cyril"}; {"Spencer"}; {"Horace"}; {"Emmit"}; {"Lynna"}; {"Charley"}; {"Fraser"}; {"Mckinley"}; {"Tiara"}; {"Damien"}; {"Linnie"}; {"Jui"}; {"Fraser"}; {"Siena"}; {"Lynna"}; {"Kelton"}; {"Kaya"} })

and returns

{1; 1; 1; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 1; 1; 0; 0; 1; 1}.

This array tells us that the three first values in Table2[First Name] are visible, however, the fourth value is hidden etc. 1 - visible, 0 (zero) - hidden.

#### Step 3 - Convert 1 to 0 (zero)

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF($B$25:B25, Table2[First Name]), "")

The COUNTIF function in this step will make sure that only unique distinct values are being returned, it contains an expanding cell reference that keeps track of previously displayed values.

IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF($B$25:B25, Table2[First Name]), "")

becomes

IF({1; 1; 1; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 1; 1; 0; 0; 1; 1}, {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, "")

and returns

{0; 0; 0; ""; ""; 0; ""; ""; ""; 0; ""; ""; ""; ""; 0; 0; ""; ""; 0; 0}

#### Step 4 - Find position

The MATCH function returns a number representing the position of the first 0 (zero) in the array.

MATCH(0, IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF($C$25:C25, Table2[First Name]),""), 0)

becomes

MATCH(0, {0; 0; 0; ""; ""; 0; ""; ""; ""; 0; ""; ""; ""; ""; 0; 0; ""; ""; 0; 0}, 0)

and returns 1.

#### Step 5 - Return value

The INDEX function returns a value based on cell reference and a row and column number.

INDEX(Table2[First Name], MATCH(0, IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF($C$25:C25, Table2[First Name]),""), 0))

becomes

INDEX(Table2[First Name], 1)

and returns "Kaya" in cell B26.

### User defined Function

User defined function in cell range A26:A31:

This formula is also entered as an array formula.

#### User defined Function Syntax

FilterUniqueSortTable(rng)

#### Arguments

Parameter |
Text |

rng |
Required. A cell reference to the range in the Excel defined Table you want to extract unique distinct values from. |

#### VBA Code

'Name custom function and parameters Function FilterUniqueSortTable(rng As Range) 'Declare variables and data types Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single 'Redimension array variable temp in order to be able to expand the variable later on ReDim temp(0) 'Enable error handling On Error Resume Next 'Iterate thorugh each value in cell range For Each Value In rng 'Check if cell is not empty and visible If Len(Value) > 0 And Value.EntireRow.Hidden = False Then 'Add value to collection, this line will return an error if the value already exists in the collection ucoll.Add Value, CStr(Value) End If 'Continue with next value Next Value 'Disable error handling On Error GoTo 0 'Iterate through each value in collection For Each Value In ucoll 'Save value to array variable temp temp(UBound(temp)) = Value 'Add another container to array variable temp ReDim Preserve temp(UBound(temp) + 1) 'Continue with next value Next Value 'Remove last item from array variable temp ReDim Preserve temp(UBound(temp) - 1) 'Count the number of rows the UDF is entered in by the user and save to variable iRows iRows = Range(Application.Caller.Address).Rows.Count 'Use UDF SelectionSort to sort values from A to Z SelectionSort temp 'Add items so the array variable temp has the same number of rows as the entered UDF and save blank values to items that contain nothing For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i 'Rearrange values in array variable temp and return them to worksheet FilterUniqueSortTable = Application.Transpose(temp) End Function

#### Where to copy code?

Press Alt+F11 to open VB Editor.

- Double press with left mouse button on your workbook in the Project Explorer.
- Press with left mouse button on "Insert" on the menu and then press with left mouse button on "Module".
- Paste above code to code module.
- Exit VB Editor and return to Excel.

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

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

### 19 Responses to “Extract unique distinct values from a filtered Excel defined Table [UDF and 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

Oscar, what is this line supposed to be doing? I have tried hard to understand this application.caller function, but can't make any sense of it.

iRows = Range(Application.Caller.Address).Rows.Count

appreciate your insight on this.

thanks so much for your tutorials again! They are as always very insightful!

Chrisham,

Thank you!

If you enter the udf in cell range A26:A30, Range(Application.Caller.Address).Rows.Count returns 5.

Application.Caller.Address returns $A$26:$A$30

Hi Oscar,

I'm a bit of a newbie trying to learn this and using your formulas and VB above I get an error in the array formulas, it doesn't like the Table2[First Name]. Problem can be that I use Swedish Excel?

I understand that Table2 is the name of the table but [First Name] what does it do and do you know if I need to translate it?

Per,

Problem can be that I use Swedish Excel?yes, try this formula:

I understand that Table2 is the name of the table but [First Name] what does it do and do you know if I need to translate it?[First Name] is the first header in the table (col A). Change it to your header name.

[...] Extract unique distinct values from a filtered table (udf and array formula) [...]

Mr. Oscar,

Can i have the values in the blow direction of a cells in udf

"Fraser Horace Jui Kaya Kelton"

Regards

Sudhakar,

I am not sure I understand, try this:

Dear Oscar,

Thanks for your valuable time, cheers...

Sudhakar

Mr. Oscar,

Fraser Horace Jui Kaya Kelton #N/A #N/A #N/A

Can we eliminate #N/A error from the above udf.

Thanks.

Sudhkar

Sudhakar,

Can we eliminate #N/A error from the above udf.Yes, I changed the code above.

Mr. Oscar,

Thanks for your help.

Sudhakar

Mr. Oscar,

I have one more question for the same,

Can we eliminate the blank cell or if the cell value is zero in case of alphanumeric sort.

Thanks in advance

Sudhakar

Good Day!

Kindly help me the following issues,

I am in need of a macro instead of “Function FilterUniqueSortTable(rng As Range)” because I have a data in the column say Column A1:A1000, I need to extract the unique value with sort Alpha numerically and put it across columns let us say from B1 to ZZ.

Right now I am using your brilliant function on this thread, its takes a long time when I update the values.

Thanks for your valuable time spend with us

Sudhakar

Somehow the VBA script formula throws a #NAME error for me.

I did the exact copy, just changed the name of the table I want sorted

Thanks for referring me to this post, Oscar. This is just what I needed! All I did was change the cell and range references, and I'm in business.

=INDEX(Teachers,MATCH(0,(IF(SUBTOTAL(3, OFFSET(Teachers, MATCH(ROW(Teachers), ROW (Teachers))-1, 0, 1)), MATCH(ROW(Teachers), ROW(Teachers)),""))*COUNTIF($Z$1:Z1,Teachers),0))

Is there a way to have the results display in alphabetical order when the source range (Teachers) isn't?

Rod,

Is it possible to sort the source range?

This formula can do what you ask for but not with filtered values:

https://www.get-digital-help.com/2009/09/22/unique-distinct-list-from-a-column-sorted-a-to-z-using-array-formula-in-excel/

No, the source range has to be sorted by a different field. That's OK, I'm happy with what the formula does even without the results being sorted differently than the source. Thanks for your help!

Thanks a lot for sharing this. I tried to replicate the formula with the new FILTER() function and it worked.

=FILTER(Table2[First Name],

SUBTOTAL(3, OFFSET(Table2[First Name],

MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1))

)

Oken, thank you for commenting.

It is possible to make your formula even smaller:

To extract unique distinct values use this formula: