Filter a table with an array list

[tana]

Hi,

Can you someone help me to filter a table with array list? Is that possible? Thanks,

I tried to upload the excel file but my add-in feature is restricted so I can't. Maybe I can provide some images for reference.

I have this data as a table.

then I used =SORT(UNIQUE(TblData2[Year])) to generate an array. The output is

I am trying to filter the data by the array TAKE(G3#,2). Can you please provide me help? Thanks,

 

[Holger]

As you have O365, how about using Groupby and the below might give you what you need:

Arrays - 7 Lambda Helper Function - Excel_Is_Fun.xlsx
	AC	AD	AE	AF	AG	AH	AI	AJ	AK
34	Amount	Year	Category
35	15	2024	Material			Year	Category	Amount
36	20	2024	Labour			2024	AOH	65
37	25	2024	AOH			2024	Labour	55
38	30	2024	Material			2024	Material	90
39	35	2024	Labour			2025	AOH	145
40	40	2024	AOH			2025	Labour	105
41	45	2024	Material			2025	Material	125
42	50	2025	Labour			Total		585
43	55	2025	Labour
44	60	2025	Material
45	65	2025	Material
46	70	2025	AOH
47	75	2025	AOH
48
SCAN

Cell Formulas
Range	Formula
AH35:AJ42	AH35	=GROUPBY(AD34:AE47,AC34:AC47,SUM,3,,2)
Dynamic array formulas.

 

[tana]

As you have O365, how about using Groupby and the below might give you what you need:

Arrays - 7 Lambda Helper Function - Excel_Is_Fun.xlsx
	AC	AD	AE	AF	AG	AH	AI	AJ	AK
34	Amount	Year	Category
35	15	2024	Material			Year	Category	Amount
36	20	2024	Labour			2024	AOH	65
37	25	2024	AOH			2024	Labour	55
38	30	2024	Material			2024	Material	90
39	35	2024	Labour			2025	AOH	145
40	40	2024	AOH			2025	Labour	105
41	45	2024	Material			2025	Material	125
42	50	2025	Labour			Total		585
43	55	2025	Labour
44	60	2025	Material
45	65	2025	Material
46	70	2025	AOH
47	75	2025	AOH
48
SCAN

Cell Formulas
Range	Formula
AH35:AJ42	AH35	=GROUPBY(AD34:AE47,AC34:AC47,SUM,3,,2)
Dynamic array formulas.

Thanks, @Holger.

I think I had a solution now. However, do you happen to know why the bottom "Total" is missed?

Thanks!!

 

[Holger]

a bit hard to tell, without seeing your formula and exact reference. It looks like you are grouping across rows&columns, which would suggest you use pivotby so maybe the column reference is not correct....

 

[Peter_SSs]

I am trying to filter the data by the array TAKE(G3#,2)

Is this what you were trying to do?

tana.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Amount	Year	Category						Amount	Year	Category
2	5	2024	a						5	2024	a
3	6	2023	b				2023		6	2023	b
4	3	2025	d				2024		8	2023	a
5	5	2025	d				2025		5	2024	b
6	4	2025	a						8	2023	d
7	8	2023	a						4	2024	d
8	9	2025	a						7	2023	a
9	5	2024	b						3	2024	a
10	8	2023	d						2	2023	b
11	4	2024	d
12	7	2023	a
13	6	2025	d
14	3	2024	a
15	2	2023	b
16	1	2025	d
17
Sheet1

Cell Formulas
Range	Formula
I2:K10	I2	=FILTER(TblData2,ISNUMBER(MATCH(TblData2[Year],TAKE(G3#,2),0)),"")
G3:G5	G3	=SORT(UNIQUE(TblData2[Year]))
Dynamic array formulas.

 

[tana]

a bit hard to tell, without seeing your formula and exact reference. It looks like you are grouping across rows&columns, which would suggest you use pivotby so maybe the column reference is not correct....

Yes, I used PIVOTBY, somehow, don't know why the "Total" was missed there.

 

[tana]

Is this what you were trying to do?

tana.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Amount	Year	Category						Amount	Year	Category
2	5	2024	a						5	2024	a
3	6	2023	b				2023		6	2023	b
4	3	2025	d				2024		8	2023	a
5	5	2025	d				2025		5	2024	b
6	4	2025	a						8	2023	d
7	8	2023	a						4	2024	d
8	9	2025	a						7	2023	a
9	5	2024	b						3	2024	a
10	8	2023	d						2	2023	b
11	4	2024	d
12	7	2023	a
13	6	2025	d
14	3	2024	a
15	2	2023	b
16	1	2025	d
17
Sheet1

Cell Formulas
Range	Formula
I2:K10	I2	=FILTER(TblData2,ISNUMBER(MATCH(TblData2[Year],TAKE(G3#,2),0)),"")
G3:G5	G3	=SORT(UNIQUE(TblData2[Year]))
Dynamic array formulas.

Yes, you are exactly right. I used xmatch instead of your match, somehow, I think it caused #VALUE error. I tried it with countif and it seems to be working fine now. Do you know any restriction between XMATCH and COUNTIF? Thanks,