Unique List From A Filtered List

[excelbytes]

I found on this forum the answer to the issue of how to generate a unique list from a filtered list. It was from a few years ago, so I couldn't ask a follow up question. The formula works perfectly, as follows:

=UNIQUE(FILTER(Customers[Company],SUBTOTAL(3,OFFSET(Customers[[#Headers],[Company]],ROW(Customers[Company])-ROW(Customers[[#Headers],[Company]]),0))))

It doesn't seem to matter if I use 3 or 103 in the SUBTOTAL function. Can someone explain to me how the SUBTOTAL function is only identifying the unhidden rows?

Thanks in advance,

 

[Fluff]

As you have 365 you can use this non-volatile formula

=UNIQUE(FILTER(Customers[Company],MAP(Customers[Company],LAMBDA(m,SUBTOTAL(103,m)))))

For info on subtotal have a look here SUBTOTAL Function

 

[excelbytes]

I see, the key is that SUBTOTAL automatically ignores cells that have been filtered out of view. Thanks.

Now I need to go through your alternate formula to figure how that works!

 

[Fluff]

You're welcome & thanks for the feedback.