Dec 11 2021 08:59 AM - edited Dec 11 2021 09:38 AM
The basic question is, how can I utilize FILTER next to an array to produce filtered results for a list of items? FILTER does not appear to allow me to use an array reference in the criterion. Is there a workaround? (UPDATE: I mean, is there a workaround that produces maintenance-free spill-type results.)
Example - imagine I have an array like so:
Projects
A
B
C
In a separate part of the workbook I have a database of projects and eligible volunteers like so:
Volunteers
Project Code Eligible Volunteers
A Mary
A John
B John
B Sue
C Peg
C Mary
C John
I want to use the project code as the criterion for a filter, and show the filter results next to the code, like so:
Project Code Volunteers
A Mary John
B John Sue
C Peg Mary John
I expected to use something like this:
=TRANSPOSE(INDEX(FILTER(EligibleVolunteers, EligibleVolunteers[Project Code]=Projects),,2))
But when I use array Projects in the criteria field, I get #n/a
I can rewrite the formula to use a cell reference to the Project Code, and drag it down, and it works. But that makes the spreadsheet not automatic anymore.
Is there a workaround or different approach? Also, what's the rationale for not allowing a dynamic reference in the criteria parameter?
Dec 11 2021 09:30 AM - edited Dec 11 2021 09:32 AM
Dec 11 2021 09:30 AM
Do you consider Power Query or data model PivotTable as an options ? With them that's straightforward way to generate the result.
Dec 11 2021 09:34 AM
Dec 11 2021 09:36 AM
And here is with PivotTable
Dec 11 2021 09:37 AM
@boukasa yes, Refresh All is required for such options.
Dec 11 2021 09:37 AM
Dec 11 2021 09:42 AM
You can achieve that for sure.
As you didn't provide a sample data (structured excel file) I developed that example based on your post details.
But as you can see, it is pretty simple to make the adjustments.
Dec 11 2021 10:00 AM
Dec 11 2021 10:27 AM - edited Dec 11 2021 10:33 AM
Without arrangements yes, but I prepared an example for you.
You can add new project name and new volunteer names as per sheets.
What I did was:
- There are different tables for each set of data (
To get all the projects at once you just need to type
=tblProject
So using the TEXTJOIN() formula, I can put everything in a single cell.
=TEXTJOIN(", ",TRUE,
FILTER(MainTable[Name],MainTable[Project]=I3))
Dec 11 2021 11:40 AM
SolutionIf return everything in one spill hat's with lambdas and new helper functions, something like
=LET( u, UNIQUE( MainTable[Project] ),
CHOOSE( {1,2},
u,
BYROW( IF( u = TRANSPOSE( MainTable[Project]), TRANSPOSE( MainTable[Name] ), ""),
LAMBDA(r, TEXTJOIN(", ", 1, r) ) ) ) )
Dec 11 2021 06:58 PM
Dec 11 2021 11:40 AM
SolutionIf return everything in one spill hat's with lambdas and new helper functions, something like
=LET( u, UNIQUE( MainTable[Project] ),
CHOOSE( {1,2},
u,
BYROW( IF( u = TRANSPOSE( MainTable[Project]), TRANSPOSE( MainTable[Name] ), ""),
LAMBDA(r, TEXTJOIN(", ", 1, r) ) ) ) )