Recognising a group of records and identify the oldest

[XLmuppet]

I have a large table of records which all have a unique key but many of which are duplicated. They all have a date created field too. I would like to try to write a formula (I can try VBA but lack any knowledge or talent here) to add a note to the third column which I have labelled as "Action" which will either be 'Keep' or a 'date' to delete. based on identifying similar records via the Key and keeping the oldest whilst adding a date to delete to any more recent i.e

KEY	Created	Action
370596	03/05/2018	Keep
425128	03/05/2017	Keep
425128	16/09/2017	01/05/2020
425128	23/09/2017	01/05/2020
475521	07/05/2018	Keep
475521	01/10/2018	01/05/2020
475521	17/09/2019	01/05/2020

Please can anyone help?

Julian

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Key	Created	Action		Cleanup date
2	370596	5/3/2018	Keep	Keep	5/1/2020
3	425128	5/3/2017	Keep	Keep
4	425128	9/16/2017	5/1/2020	5/1/2020
5	425128	9/23/2017	5/1/2020	5/1/2020
6	475521	5/7/2018	Keep	Keep
7	475521	10/1/2018	5/1/2020	5/1/2020
8	475521	9/17/2019	5/1/2020	5/1/2020
Sheet13

Cell Formulas
Range	Formula
C2:C8	C2	=IF(B2=MINIFS(B:B,A:A,A2),"Keep",E$2)
D2:D8	D2	=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8)),"Keep",E$2)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

MINIFS came along in Excel 2016 I believe. If your version doesn't have it, try the D2 formula instead.