Extract unique distinct values from a filtered table (udf and array formula)
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
Extract unique distinct values from a filtered table.xlsm
Related posts:
Count unique distinct values in a filtered table
Excel udf: Filter unique distinct values (case sensitive)
Recommended categories
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) [...]