Formula to find the last entry of different values that can be repeated (unsorted)

[cornea_za]

Hi All
Thanx for a great forum.
I have two columns, the first with random dates (can be repeated), and the second with different text values that is repeated randomly:
A B C (I need formula to give below results - last date)
2021/12/02 Charlie 2022/01/03
2021/12/05 Bravo 2022/01/02
2021/12/29 Charlie 2022/01/03
2022/01/02 Alpha 2022/01/04
2022/01/02 Bravo 2022/01/02
2022/01/03 Charlie 2022/01/03
2022/01/04 Alpha 2022/01/04

Will it be possible to have a formula if the dates are in random order (not required)?

Thanx in advance
Corne

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Max Date", each List.Max([Column1]), type nullable date}})
in
    #"Grouped Rows"

Book7
	A	B	C	D	E
1	Column1	Column2		Column2	Max Date
2	12/2/2021	Charlie		Charlie	1/3/2022
3	12/5/2021	Bravo		Bravo	1/2/2022
4	12/29/2021	Charlie		Alpha	1/4/2022
5	1/2/2022	Alpha
6	1/2/2022	Bravo
7	1/3/2022	Charlie
8	1/4/2022	Alpha
Sheet1

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Given that we do not know your Excel version, here are a couple of possibilities

22 01 06.xlsm
	A	B	C	D
1	Date	Value	Last Date	Last Date
2	02-Dec-21	Charlie	03-Jan-22	03-Jan-22
3	05-Dec-21	Bravo	02-Jan-22	02-Jan-22
4	29-Dec-21	Charlie	03-Jan-22	03-Jan-22
5	02-Jan-22	Alpha	04-Jan-22	04-Jan-22
6	02-Jan-22	Bravo	02-Jan-22	02-Jan-22
7	03-Jan-22	Charlie	03-Jan-22	03-Jan-22
8	04-Jan-22	Alpha	04-Jan-22	04-Jan-22
Lastest Date

Cell Formulas
Range	Formula
C2:C8	C2	=MAXIFS(A$2:A$8,B$2:B$8,B2)
D2:D8	D2	=AGGREGATE(14,6,A$2:A$8/(B$2:B$8=B2),1)