Excel Formula to highlight most recent record

[JoeRooney]

Hi All,

I need a formula to highlight the most recent record for a policy , I have included a example below.

Any help with this is greatly appreciated.

Policy	Trans_Time	Required Formula
AAA	25/10/2019 02:07:42 PM
AAA	06/12/2019 02:05:44 PM
AAA	23/12/2019 11:01:12 AM
AAA	17/01/2020 09:15:41 AM	Last
BBB	09/06/2020 03:18:04 PM
BBB	10/07/2020 05:39:06 PM
BBB	12/08/2020 01:46:49 PM
BBB	10/09/2020 12:05:46 PM
BBB	10/10/2020 12:07:26 PM
BBB	28/11/2020 12:06:15 PM	Last
CCC	18/12/2017 04:37:01 PM
CCC	06/03/2018 12:32:23 PM	Last

 

[etaf]

how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
	A	B	C	D
1	AAA	10/25/19 14:07
2	AAA	12/6/19 14:05
3	AAA	12/23/19 11:01
4	AAA	1/17/20 9:15	Last	last
5	BBB	6/9/20 15:18
6	BBB	7/10/20 17:39
7	BBB	8/12/20 13:46
8	BBB	9/10/20 12:05
9	BBB	10/10/20 12:07
10	BBB	11/28/20 12:06	Last	last
11	CCC	12/18/17 16:37
12	CCC	3/6/18 12:32	Last	last
13	CCC	1/17/20 9:15
Sheet1

Cell Formulas
Range	Formula
D1:D13	D1	=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

 

[JoeRooney]

how about
=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Book13
	A	B	C	D
1	AAA	10/25/19 14:07
2	AAA	12/6/19 14:05
3	AAA	12/23/19 11:01
4	AAA	1/17/20 9:15	Last	last
5	BBB	6/9/20 15:18
6	BBB	7/10/20 17:39
7	BBB	8/12/20 13:46
8	BBB	9/10/20 12:05
9	BBB	10/10/20 12:07
10	BBB	11/28/20 12:06	Last	last
11	CCC	12/18/17 16:37
12	CCC	3/6/18 12:32	Last	last
13	CCC	1/17/20 9:15
Sheet1

Cell Formulas
Range	Formula
D1:D13	D1	=IF(A1&B1=A1&MAXIFS($B$1:$B$12,$A$1:$A$12,A1),"last","")

Works great

 

[etaf]

you are welcome