Using the FILTER function on a result returned by the FILTER function.

[Herakles]

Following in from my previous post here, how do I apply a filter to the results of the filter and subsequently populate the array.

This code returns rows 2 to 9 from table tblNames, I now want to filter those rows that have a value of 'Item 3' in the first column.

Thanks.

test.xlsm
	A	B	C
1	Name	Group	Wins
2	Peter	C	1
3	Item 2	A	6
4	Item 3	C	4
5	Item 3	C	5
6	Item 3	A	7
7	Item 6	A	9
8	Item 7	C	3
9	Item 8	A	7
10	Helen	B	4
11	Item 10	C	6
12	Item 3	B	3
13	Item 3	B	5
14	Item 11	B	7
Names

Private Sub testIt()
Dim arr As Variant

arr = Evaluate("IFERROR(chooserows(FILTER(tblNames,tblNames[Group]<>""""), SEQUENCE(9,1,2,1)), """")")

End Sub

 

[RoryA]

You could use something like:

"LET(a,IFERROR(CHOOSEROWS(FILTER(tblNames,tblNames[Group]<>""""), SEQUENCE(9,1,2,1)), """"),FILTER(a,INDEX(a,0,1)=""item 3""))

 

[Alex Blakenburg]

I thought it might be safer to do the Iferror last.

Private Sub testIt()
Dim arr As Variant

arr = Evaluate("IFERROR(LET(fltr,CHOOSEROWS(FILTER(tblNames,tblNames[Group]<>""""),SEQUENCE(9,1,2,1)),FILTER(fltr,CHOOSECOLS(fltr,1)=""Item 3"")), """")")

End Sub