## Extract unique distinct values in a filtered list

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.

#### Table of Contents

- List unique distinct values in a filtered Excel defined Table - Excel 365
- List unique distinct values - Autofilter
- List unique distinct values in a filtered Excel defined Table - earlier Excel versions
- List unique distinct values in a filtered Excel defined Table - User Defined Function
- Get Excel *.xlsx file

## 1. Extract unique distinct values in a filtered Excel defined Table - Excel 365

Excel 365 dynamic array formula in cell B26:

Dynamic array formulas and spilled array behavior

### Explaining the formula in cell B26

#### Step 1 - Count rows

Table2[First Name] is a structured reference meaning a cell reference to an Excel Table. This particular name references a column with column header name "First Name" in Excel Table Table2.

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(Table2[First Name])

becomes

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

and returns

20.

#### Step 2 - Create a sequence from 1 to n

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROWS(Table2[First Name]))

becomes

SEQUENCE(20)

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}.

#### Step 3 - Create a sequence from 0 (zero) to n-1

The minus sign lets you subtract numbers in an Excel formula, this works fine with arrays as well.

SEQUENCE(ROWS(Table2[First Name]))-1

becomes

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}-1

and returns

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}.

#### Step 4 - Create an array that works with the SUBTOTAL function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

Function syntax: OFFSET(reference,rows,columns,[height],[width])

OFFSET(Table2[First Name], SEQUENCE(ROWS(Table2[First Name]))-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)

and returns

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

#### Step 5 - Check which values are displayed

The SUBTOTAL function returns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.

Function syntax: SUBTOTAL(function_num, ref1, ...)

SUBTOTAL(3, OFFSET(Table2[First Name], SEQUENCE(ROWS(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}.

#### Step 6 - Filter values based on count

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(Table2[First Name], SUBTOTAL(3, OFFSET(Table2[First Name], SEQUENCE(ROWS(Table2[First Name]))-1, 0, 1)))

becomes

FILTER(Table2[First Name], {1; 1; 1; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 1; 1; 0; 0; 1; 1})

becomes

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

and returns

{"Kaya"; "Fraser"; "Jui"; "Horace"; "Fraser"; "Jui"; "Fraser"; "Kelton"; "Kaya"}.

#### Step 7 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(Table2[First Name], SUBTOTAL(3, OFFSET(Table2[First Name], SEQUENCE(ROWS(Table2[First Name]))-1, 0, 1)))

becomes

UNIQUE({"Kaya"; "Fraser"; "Jui"; "Horace"; "Fraser"; "Jui"; "Fraser"; "Kelton"; "Kaya"})

and returns

{"Kaya"; "Fraser"; "Jui"; "Horace"; "Kelton"}.

#### Step 8 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

UNIQUE(FILTER(Table2[First Name], SUBTOTAL(3, OFFSET(Table2[First Name], SEQUENCE(ROWS(Table2[First Name]))-1, 0, 1)))

x - Table2[First Name]

LET(x, Table2[First Name], UNIQUE(FILTER(x, SUBTOTAL(3, OFFSET(x, SEQUENCE(ROWS(x))-1, 0, 1))))

## 2. Extract unique distinct values using a formula - Autofilter

The formulas work for Excel's Autofilter feature as well. The formula for earlier Excel versions is the same as in section three below but the structured references are now cell references. Read section three for a formula explanation.

Array formula in cell B26:

The formula for Excel 365 is the same as in section one above but the structured reference is now a cell reference. Read section one for a formula explanation.

Excel 365 dynamic array formula in cell D26:

### Recommended reading

## 3. Extract unique distinct values from a filtered Excel defined Table - 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

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.

### 3.1 Explaining array formula in cell B26

#### Step 1 - Create an 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.

## 4. Extract unique distinct values from a filtered Excel defined Table - User defined Function

User defined function in cell range A26:A31:

This formula is also entered as an array formula.

### 4.1 User defined Function Syntax

FilterUniqueSortTable(rng)

### 4.2 Arguments

Parameter |
Text |

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

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

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

### Table category

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

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

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]

This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

### Unique distinct values category

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

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

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]

### User defined function category

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

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

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

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

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]

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

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

The SUBSTITUTE and REPLACE functions can only handle one string, this article demonstrates two ways to handle more than one […]

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

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

In this vba tutorial I am going to show you how to return values from an udf, depending on where […]

This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## How to use Excel Tables

## Excel categories

### 19 Responses to “Extract unique distinct values in a filtered list”

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