Filter a table with an array list

T

tana

Board Regular
Joined
Jan 22, 2021
Messages
63
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: 4
Sort by date Sort by votes
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
Holger said:
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.
Click to expand...
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
tana said:
I am trying to filter the data by the array TAKE(G3#,2)
Click to expand...
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
Holger said:
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....
Click to expand...
Yes, I used PIVOTBY, somehow, don't know why the "Total" was missed there.
 
Upvote 0
Peter_SSs said:
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.
Click to expand...
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
tana said:
I used xmatch instead of your match, somehow, I think it caused #VALUE error.
Click to expand...
In this formula I can't see there should be any difference between using MATCH or XMATCH.

tana said:
I tried it with countif and it seems to be working fine now. Do you know any restriction between XMATCH and COUNTIF?
Click to expand...
I would need to see the formula and some sample data to try to understand what you are getting at.
 
Upvote 0
Peter_SSs said:
In this formula I can't see there should be any difference between using MATCH or XMATCH.


I would need to see the formula and some sample data to try to understand what you are getting at.
Click to expand...
Thanks for your reply. Sorry I can't share the data due to confidentiality.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Array Overwriting Totals Row and Existing Data below Table
2
Replies
11
Views
331
Alex Blakenburg
A
SanjayGMusafir
  • Question Question
Filtering HSTACK(ed) List
Replies
1
Views
342
SanjayGMusafir
SanjayGMusafir
M
  • Solved
Filter excluded rows in array formula
Replies
5
Views
133
Eric W
Eric W
MikeMcCollister
  • Solved
How do I compress table to aggregate values for unique names?
Replies
6
Views
75
Fluff
Fluff
S
  • Question Question
Dynamic Filters When List Includes "All"
Replies
5
Views
150
Cubist
Cubist

Share this page

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