Filter function alternative in Excel 2019

[Dimuster]

Hi,

I have a excel sheet that imports a large amount of data to a table through power query. In another sheet, there's a cell where I enter a date & need to filter the data in the table with the entered date.

Since the Filter excel function is available in Office 2021 & 365 only, How can I filter my data dynamically as when I change the date in that particular cell?

 

[Peter_SSs]

Welcome to the MrExcel board!

I have done this on a single sheet but you should be able to adapt it to two.

23 01 16.xlsm
	A	B	C	D	E	F	G	H	I
1	Code	Date	Amount			Date	Code	Date	Amount
2	Code 1	1/01/2023	Amount 1			4/01/2023	Code 3	4/01/2023	Amount 3
3	Code 2	3/01/2023	Amount 2				Code 6	4/01/2023	Amount 6
4	Code 3	4/01/2023	Amount 3				Code 8	4/01/2023	Amount 8
5	Code 4	1/01/2023	Amount 4				Code 12	4/01/2023	Amount 12
6	Code 5	3/01/2023	Amount 5
7	Code 6	4/01/2023	Amount 6
8	Code 7	3/01/2023	Amount 7
9	Code 8	4/01/2023	Amount 8
10	Code 9	1/01/2023	Amount 9
11	Code 10	2/01/2023	Amount 10
12	Code 11	3/01/2023	Amount 11
13	Code 12	4/01/2023	Amount 12
Filter

Cell Formulas
Range	Formula
G2:I13	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$100)/($B$2:$B$100=$F$2),ROWS(G$2:G2))),"")

 

[Dimuster]

Welcome to the MrExcel board!

I have done this on a single sheet but you should be able to adapt it to two.

23 01 16.xlsm
	A	B	C	D	E	F	G	H	I
1	Code	Date	Amount			Date	Code	Date	Amount
2	Code 1	1/01/2023	Amount 1			4/01/2023	Code 3	4/01/2023	Amount 3
3	Code 2	3/01/2023	Amount 2				Code 6	4/01/2023	Amount 6
4	Code 3	4/01/2023	Amount 3				Code 8	4/01/2023	Amount 8
5	Code 4	1/01/2023	Amount 4				Code 12	4/01/2023	Amount 12
6	Code 5	3/01/2023	Amount 5
7	Code 6	4/01/2023	Amount 6
8	Code 7	3/01/2023	Amount 7
9	Code 8	4/01/2023	Amount 8
10	Code 9	1/01/2023	Amount 9
11	Code 10	2/01/2023	Amount 10
12	Code 11	3/01/2023	Amount 11
13	Code 12	4/01/2023	Amount 12
Filter

Cell Formulas
Range	Formula
G2:I13	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$100)/($B$2:$B$100=$F$2),ROWS(G$2:G2))),"")

Thank you very much & this will do what I want.

If I may ask some further, I import the data into the table from data files available in a folder. Is there a way to directly extract only the data relevant to the date I have in the cell (F2 in your example) without extracting the whole data to the excel sheet & apply your formula to the full table?

What I want is the power query output only the data pertaining to the date I want to the Excel sheet.

 

[Peter_SSs]

I would suggest that you start a new thread in the Power Tools forum, provide details of your existing power query and outline your desired modification.