Returning the cell with the highest value from adjacent column

[stillanewbie]

I need to return the most recent date that is associated to a columns inputs. So in the example: for every Flavor input (of which there can be multiple of the same) there is a corresponding date. I would like to return the most recent date associated with each flavor.

Sorry if I haven't explained this too well but I think the below table will give you a good idea of what I am trying to achieve. Thanks!

DATE	Flavor	Expected Output
Dec-12	Choc	Dec-18
Dec-14	Vanilla	Dec-20
Dec-18	Choc	Dec-18
Dec-17	Strawberry	Dec-17
Dec-20	Vanilla	Dec-20
Dec-09	Strawberry	Dec-17

 

[alansidman]

with power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Flavor"}, {{"Max Date", each List.Max([DATE]), type nullable date}, {"Data", each _, type table [DATE=nullable date, Flavor=text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"DATE"}, {"DATE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Flavor", "DATE", "Max Date"})
in
    #"Reordered Columns"

Book1
	D	E	F
1	Flavor	DATE	Max Date
2	Choc	12/12/2021	12/18/2021
3	Choc	12/18/2021	12/18/2021
4	Vanilla	12/14/2021	12/20/2021
5	Vanilla	12/20/2021	12/20/2021
6	Strawberry	12/17/2021	12/17/2021
7	Strawberry	12/9/2021	12/17/2021
Sheet1

 

[FormR]

Hi, welcome to the forum!

Here is another option you could try.

Book3
	A	B	C
1	DATE	Flavor	Expected Output
2	01/12/2012	Choc	01/12/2018
3	01/12/2014	Vanilla	01/12/2020
4	01/12/2018	Choc	01/12/2018
5	01/12/2017	Strawberry	01/12/2017
6	01/12/2020	Vanilla	01/12/2020
7	01/12/2009	Strawberry	01/12/2017
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=MAXIFS(A:A,B:B,B2)

 

[Fluff]

Another option for xl2016

+Fluff 1.xlsm
	A	B	C
1	DATE	Flavor	Expected Output
2	01/12/2012	Choc	01/12/2018
3	01/12/2014	Vanilla	01/12/2020
4	01/12/2018	Choc	01/12/2018
5	01/12/2017	Strawberry	01/12/2017
6	01/12/2020	Vanilla	01/12/2020
7	01/12/2009	Strawberry	01/12/2017
Lists

Cell Formulas
Range	Formula
C2:C7	C2	=AGGREGATE(14,6,$A$2:$A$7/($B$2:$B$7=B2),1)