Using SPILL in FILTER formula

[RonnieS]

Hi,

Is there a way to use SPILL in a FILTER formula?

I've got a SPILL list of Unique locations.
=SORT(UNIQUE(TablePrint[Location]))

Secondly I want to show a dynamic list of Unique CaseID's for that locations.
=ROWS(UNIQUE(FILTER(TablePrint[Case ID];TablePrint[Location]=AO6)))

This works fine, but I have to fill the series manually to the same size as the first SPILL list.
I need to get that dynamically.

The Cell reference 'AO6' references to the first cell of the UNIQUE SPILL list of the first formula.
If I change this reference to 'AO6#' it give's a "#N/A".

I'm out of thoughts, so any help will be appreciated.

Ron

 

[Fluff]

Hi & welcome to MrExcel.
Are you trying to get a list of values (as per your description) or a count of those values (as per your formula)?

 

[RonnieS]

Hi & welcome to MrExcel.
Are you trying to get a list of values (as per your description) or a count of those values (as per your formula)?

I need a count of unique CaseID's for that specific location (SPILL list of locations).
Thats why the ROWS in front of the second formula. I tried SUMPRODUCT as well, but that also didn't work

I will try to make test XLS, because the current data is customer info I can't share

 

[Fluff]

Ok, how about

=BYROW(AO6#,LAMBDA(br,ROWS(UNIQUE(FILTER(TablePrint[Case ID],TablePrint[Location]=br)))))

 

[RonnieS]

Ok, how about

=BYROW(AO6#,LAMBDA(br,ROWS(UNIQUE(FILTER(TablePrint[Case ID],TablePrint[Location]=br)))))

That did the trick!!
Works absolutely perfect. Many Thanks
Now trying to break down the formula and understand what's happening, but I'll be fine.

 

[Fluff]

You're welcome & thanks for the feedback.