Dynamic Filter by row

[BrettJH]

Hi,

I'm hoping someone can help me with this query.

I'm struggling to dynamically filter a table by a list of the Unique names and return the entire row from the table where the Date is the Max date.

I have a formula that filters as expected if I copy it down, however I really need this to be dynamic as the number of names will vary
=LET(dta, FILTER(Table1,(Table1[Name]=H5)),FILTER(dta,INDEX(dta,,3)=MAX(INDEX(dta,,3))))

My problems arise when I try to use it in a Lambda to dynamically process each row of the unique names.
I'm clearley not understanding something fundamental when using Byrow or Map with a Lambda to Filter rowsbased on multiple criteria.

Here's an eaxmple of the data, Table1 has these columns:

Name	ID	Date	Rate
John Doe	100​	01/01/2022​	£27​
John Doe	100​	01/06/2020​	£24​
John Doe	100​	01/01/2021​	£25​
John Doe	100​	01/01/2023​	£31​
John Doe	100​	07/10/2019​	£22​
Jane Smith	101​	19/03/2020​	£38​
Jane Smith	101​	01/01/2021​	£40​
Jane Smith	101​	01/01/2022​	£46​
Richard Roe	102​	01/05/2019​	£35​
Richard Roe	102​	01/05/2018​	£34​
Richard Roe	102​	29/08/2017​	£33​
Richard Roe	102​	01/01/2023​	£40​
Richard Roe	102​	01/01/2022​	£38​
Richard Roe	102​	01/01/2021​	£37​

The unique list of names in H5:
=UNIQUE(Table1[Name])

Unique Names

John Doe

Jane Smith

Richard Roe

and the expected result:

Name	ID	Date	Rate
John Doe	100​	01/01/2023​	31​
Jane Smith	101​	01/01/2022​	46​
Richard Roe	102​	01/01/2023​	40​

This attempt produces a #CALC! error, same if I use Byrow
=MAP(H5#,LAMBDA(r,LET(dta, FILTER(Table1,(Table1[Name]=r)),x,FILTER(dta,INDEX(dta,,3)=MAX(INDEX(dta,,3))),
x)))

This produces the correct reuslts, but all text is concatenated in a single cell in one column:
=MAP(H5#,LAMBDA(r,LET(dta, FILTER(Table1,(Table1[Name]=r)),x,FILTER(dta,INDEX(dta,,3)=MAX(INDEX(dta,,3))),
ARRAYTOTEXT(x))))

John Doe, 100, 44927, 31
Jane Smith, 101, 44562, 46
Richard Roe, 102, 44927, 40

And any attempts to Splittext produce errors.

What am I missing?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	ID	Date	Rate
2	John Doe	100	01/01/2022	£27			Name	ID	Date	Rate
3	John Doe	100	01/06/2020	£24			John Doe	100	01/01/2023	31
4	John Doe	100	01/01/2021	£25			Jane Smith	101	01/01/2022	46
5	John Doe	100	01/01/2023	£31			Richard Roe	102	01/01/2023	40
6	John Doe	100	07/10/2019	£22
7	Jane Smith	101	19/03/2020	£38
8	Jane Smith	101	01/01/2021	£40
9	Jane Smith	101	01/01/2022	£46
10	Richard Roe	102	01/05/2019	£35
11	Richard Roe	102	01/05/2018	£34
12	Richard Roe	102	29/08/2017	£33
13	Richard Roe	102	01/01/2023	£40
14	Richard Roe	102	01/01/2022	£38
15	Richard Roe	102	01/01/2021	£37
Master

Cell Formulas
Range	Formula
G2:J5	G2	=REDUCE(Table1[#Headers],UNIQUE(Table1[Name]),LAMBDA(x,y,VSTACK(x,FILTER(Table1,(Table1[Name]=y)*(Table1[Date]=MAXIFS(Table1[Date],Table1[Name],y))))))
Dynamic array formulas.

 

[BrettJH]

Absolutely perfect!

Many thanks Fluff

I clearly need to understand the Reduce function more

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alansidman]

Late to the party, but offering up an alternative with Power Query for any that are interested

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    GR = Table.Group(#"Changed Type", {"Name"}, {{"Max Date", each List.Max([Date]), type nullable date}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    MQ = Table.NestedJoin(GR, {"Name","Max Date"}, #"Changed Type2", {"Name","Date"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Rate"}, {"Rate"})
in
    #"Expanded Table2"

Book5
	A	B	C	D	E	F	G	H
1	Name	ID	Date	Rate		Name	Max Date	Rate
2	John Doe	100	1/1/2022	£27		John Doe	1/1/2023	£31
3	John Doe	100	6/1/2020	£24		Jane Smith	1/1/2022	£46
4	John Doe	100	1/1/2021	£25		Richard Roe	1/1/2023	£40
5	John Doe	100	1/1/2023	£31
6	John Doe	100	10/7/2019	£22
7	Jane Smith	101	3/19/2020	£38
8	Jane Smith	101	1/1/2021	£40
9	Jane Smith	101	1/1/2022	£46
10	Richard Roe	102	5/1/2019	£35
11	Richard Roe	102	5/1/2018	£34
12	Richard Roe	102	8/29/2017	£33
13	Richard Roe	102	1/1/2023	£40
14	Richard Roe	102	1/1/2022	£38
15	Richard Roe	102	1/1/2021	£37
Sheet1

 

[BrettJH]

Thanks Alan,

Appreciate that

That could be useful as I'm retrieving the source table via PowerQuery.
However I think I'll need to keep all the data and changes for other monthly calucations, e.g. a comp rate changes in July I will need the previous rate for May and June and the new rate moving forward until the next change. Not 100% sure yet

b