## Extract unique distinct values from a filtered table [udf and array formula]

*Article updated on January 20, 2018*

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

**Answer:**

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

**Array Formula in cell B26:**

**How to create an array formula**

- Select cell B26
- Type array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell B26
- Copy cell (Ctrl + c)
- Select cell range B27:B30
- Paste (Ctrl + v)

**User defined function FilterUniqueSortTable(range)**

Function FilterUniqueSortTable(rng As Range) Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single ReDim temp(0) On Error Resume Next For Each Value In rng If Len(Value) > 0 And Value.EntireRow.Hidden = False Then ucoll.Add Value, CStr(Value) End If Next Value On Error GoTo 0 For Each Value In ucoll temp(UBound(temp)) = Value ReDim Preserve temp(UBound(temp) + 1) Next Value ReDim Preserve temp(UBound(temp) - 1) iRows = Range(Application.Caller.Address).Rows.Count SelectionSort temp For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i FilterUniqueSortTable = Application.Transpose(temp) End Function</pre>

**Where to copy code?**

Press Alt+F11

**User defined function in cell range A26:A31:**

=FilterUniqueSortTable(Table2[First Name])

**How to create array formula**

- Select cell range A26:A31
- Type above formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**Download excel 2007 *.xlsm file**

### 17 Responses to “Extract unique distinct values from a filtered table [udf and array formula]”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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!