Filter a table with an array list

tana

Board Regular
Joined
Jan 22, 2021
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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.

1740965056293.png


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

1740965427491.png


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

Attachments

  • 1740965132932.png
    1740965132932.png
    6 KB · Views: 3
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
ACADAEAFAGAHAIAJAK
34AmountYearCategory
35152024MaterialYearCategoryAmount
36202024Labour2024AOH65
37252024AOH2024Labour55
38302024Material2024Material90
39352024Labour2025AOH145
40402024AOH2025Labour105
41452024Material2025Material125
42502025LabourTotal585
43552025Labour
44602025Material
45652025Material
46702025AOH
47752025AOH
48
SCAN
Cell Formulas
RangeFormula
AH35:AJ42AH35=GROUPBY(AD34:AE47,AC34:AC47,SUM,3,,2)
Dynamic array formulas.
 
Upvote 0
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
ACADAEAFAGAHAIAJAK
34AmountYearCategory
35152024MaterialYearCategoryAmount
36202024Labour2024AOH65
37252024AOH2024Labour55
38302024Material2024Material90
39352024Labour2025AOH145
40402024AOH2025Labour105
41452024Material2025Material125
42502025LabourTotal585
43552025Labour
44602025Material
45652025Material
46702025AOH
47752025AOH
48
SCAN
Cell Formulas
RangeFormula
AH35:AJ42AH35=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?

1740972078091.png


Thanks!!
 

Attachments

  • 1740972057909.png
    1740972057909.png
    14.8 KB · Views: 2
Upvote 0
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....
 
Upvote 0
I am trying to filter the data by the array TAKE(G3#,2)
Is this what you were trying to do?

tana.xlsm
ABCDEFGHIJK
1AmountYearCategoryAmountYearCategory
252024a52024a
362023b202362023b
432025d202482023a
552025d202552024b
642025a82023d
782023a42024d
892025a72023a
952024b32024a
1082023d22023b
1142024d
1272023a
1362025d
1432024a
1522023b
1612025d
17
Sheet1
Cell Formulas
RangeFormula
I2:K10I2=FILTER(TblData2,ISNUMBER(MATCH(TblData2[Year],TAKE(G3#,2),0)),"")
G3:G5G3=SORT(UNIQUE(TblData2[Year]))
Dynamic array formulas.
 
Upvote 0
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.
 
Upvote 0
Is this what you were trying to do?

tana.xlsm
ABCDEFGHIJK
1AmountYearCategoryAmountYearCategory
252024a52024a
362023b202362023b
432025d202482023a
552025d202552024b
642025a82023d
782023a42024d
892025a72023a
952024b32024a
1082023d22023b
1142024d
1272023a
1362025d
1432024a
1522023b
1612025d
17
Sheet1
Cell Formulas
RangeFormula
I2:K10I2=FILTER(TblData2,ISNUMBER(MATCH(TblData2[Year],TAKE(G3#,2),0)),"")
G3:G5G3=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,
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top