Extract Unique Values from Filtered List

[kruss777]

I'm looking to return a listing of unique values from an auto-filtered list.

I have a range of customer data (address, phone number, etc...) that I'm sorting by individual county. The list has 67,950 records (rows of data) with countless records falling in the same counties randomly throughout the list (there are a total of 83 counties overall). When I auto-filter the list to exclude counties whose data I don't want to see, the list is still huge (at any given time I may be looking at a couple of dozen counties in a filtered list), so I want to have a quick reference showing what counties are still visible.

All of my counties are listed in column "I" (I5:I67950).

This must be a tough one because I can't find a reference to this type of solution ANYWHERE!

Any ideas?


Ken

[AliGW]

Welcome to the forum.

Easy with PowerQuery. Can you share a sample workbook?

[kruss777]

Here is a sample workbook.

[AliGW]

I used PowerQuery first to filter out the counties I wasn't interested in (I chose Oakland and Macomb), then I removed extraneous columns and duplicates from the resulting list.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LEGAL_NAME", type text}, {"DBA_NAME", type text}, {"CARRIER_OPERATION", type text}, {"HM_FLAG", type text}, {"PC_FLAG", type text}, {"PHY_STREET", type text}, {"PHY_CITY", type text}, {"PHY_COUNTY", type text}, {"PHY_STATE", type text}, {"PHY_ZIP", Int64.Type}, {"PHY_ZIP_PLUS_FOUR", Int64.Type}, {"PHY_COUNTRY", type text}, {"MAILING_STREET", type any}, {"MAILING_CITY", type text}, {"MAILING_STATE", type text}, {"MAILING_ZIP", Int64.Type}, {"MAILING_ZIP_PLUS_FOUR", Int64.Type}, {"MAILING_COUNTRY", type text}, {"MCS150_DATE", type datetime}, {"MCS150_MILEAGE", Int64.Type}, {"MCS150_MILEAGE_YEAR", Int64.Type}, {"ADD_DATE", type datetime}, {"OIC_STATE", type text}, {"NBR_POWER_UNIT", Int64.Type}, {"DRIVER_TOTAL", Int64.Type}, {"Column1", type any}, {"TRUE/FALSE", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([PHY_COUNTY] = "LAPEER" or [PHY_COUNTY] = "SAINT CLAIR")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"LEGAL_NAME", "DBA_NAME", "CARRIER_OPERATION", "HM_FLAG", "PC_FLAG", "PHY_STREET", "PHY_CITY", "PHY_STATE", "PHY_ZIP", "PHY_ZIP_PLUS_FOUR", "PHY_COUNTRY", "MAILING_STREET", "MAILING_CITY", "MAILING_STATE", "MAILING_ZIP", "MAILING_ZIP_PLUS_FOUR", "MAILING_COUNTRY", "MCS150_DATE", "MCS150_MILEAGE", "MCS150_MILEAGE_YEAR", "ADD_DATE", "OIC_STATE", "NBR_POWER_UNIT", "DRIVER_TOTAL", "Column1", "TRUE/FALSE"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Result:

Excel 2016 (Windows) 32 bit

	A
1	PHY_COUNTY
2	SAINT CLAIR
3	LAPEER

Sheet: Filtered Counties

[kruss777]

Thank you.

But I think I failed to fully explain exactly what I'm after. Let me explain further...

I want the list of VISIBLE counties from the auto-filtered list to appear on the original tab (either above or below the visible filtered data range) for quick reference so that I can see at a glance what counties' data is displayed. I'm not looking for just a simple listing of visible counties by itself, but a listing of visible counties that updates as the auto-filtered list selection criteria changes (I often add/subtract counties from the filtered list as I'm working). I was attempting to accomplish it with a formula but haven't been able to find a formula that works like SUMPRODUCT, SUBTOTAL, or INDEX. The point it for it to update the summary list as the autofilter criteria is changed.

Does that help?

Ken

[kvsrinivasamurthy]

UDF will do?

[Bo_Ry]

Not sure this is what you want.
Please try at H1
=IFERROR(INDEX($H$6:$H$1500,SMALL(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($H$6,ROW($H$6:$H$1500)-ROW($H$6),)),MATCH($H$6:$H$1500,$H$6:$H$1500,)),ROW($H$6:$H$1500)-ROW($H$6)),ROW($H$6:$H$1500)-ROW($H$6)),ROW(H1))),"")
Press Ctrl+Shift+Enter and drag to H4

[kruss777]

Not sure this is what you want.

This is EXACTLY what I wanted! Amazing!

Thank you very kindly!!!!!


Ken