Identify latest record based on criteria

[JoshLyman]

I have a dataset where sometimes a Regno will have multiple entries (highlighted in red below). I need to be able to identify whether the most recent entry for a given Regno (as determined by the most recent LEARNSTARTDATE) reflects a COMPSTATUS of 6.

For instance
A671172 would not meet this criteria, because the most recent entry has a COMPSTATUS of 3
B312726 would not meet this criteria, because the most recent entry has a COMPSTATUS of 1
B930532 would meet this criteria, because the most recent entry has a COMPSTATUS of 6

2223-LearningDelivery (2).csv
	A	B	C	D	E
1	Regno	LEARNSTARTDATE	LEARNPLANENDDATE	COMPSTATUS	LATEST
2	A567883	13/10/2021	31/07/2023	1
3	A671172	22/01/2020	30/09/2021	6
4	A671172	23/06/2022	31/01/2023	3
5	B018353	30/09/2019	30/09/2022	1
6	B119158	07/10/2020	30/04/2023	1
7	B216978	13/10/2021	31/07/2023	1
8	B218433	05/10/2020	05/10/2023	1
9	B219012	04/10/2021	04/10/2024	1
10	B312726	12/10/2022	31/07/2024	6
11	B312726	27/02/2023	31/07/2024	1
12	B418353	07/02/2022	07/02/2025	1
13	B419754	03/10/2022	03/10/2025	1
14	B426123	13/10/2021	31/07/2023	1
15	B831449	10/10/2018	31/07/2021	6
16	B831449	13/10/2020	31/07/2021	6
17	B831449	13/10/2021	31/12/2022	1
18	B831530	09/10/2018	31/07/2021	6
19	B831530	30/09/2019	29/04/2022	6
20	B831530	01/03/2021	31/07/2023	1
21	B831830	10/10/2018	31/07/2021	6
22	B831830	13/10/2020	31/07/2021	6
23	B831830	13/10/2021	31/12/2022	2
24	B832070	10/10/2018	31/07/2021	6
25	B832070	28/06/2021	31/07/2022	2
26	B833681	27/02/2019	30/11/2021	6
27	B833681	01/09/2022	10/03/2023	1
28	B833744	27/02/2019	30/11/2021	2
29	B930193	09/10/2019	29/04/2022	2
30	B930454	09/10/2019	29/04/2022	2
31	B930532	16/10/2019	29/04/2022	6
32	B930532	02/08/2021	29/04/2023	6
2223-LearningDelivery (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

Hope this makes sense!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Regno	LEARNSTARTDATE	LEARNPLANENDDATE	COMPSTATUS	LATEST
2	A567883	13/10/2021	31/07/2023	1	FALSE
3	A671172	22/01/2020	30/09/2021	6	FALSE
4	A671172	23/06/2022	31/01/2023	3	FALSE
5	B018353	30/09/2019	30/09/2022	1	FALSE
6	B119158	07/10/2020	30/04/2023	1	FALSE
7	B216978	13/10/2021	31/07/2023	1	FALSE
8	B218433	05/10/2020	05/10/2023	1	FALSE
9	B219012	04/10/2021	04/10/2024	1	FALSE
10	B312726	12/10/2022	31/07/2024	6	FALSE
11	B312726	27/02/2023	31/07/2024	1	FALSE
12	B418353	07/02/2022	07/02/2025	1	FALSE
13	B419754	03/10/2022	03/10/2025	1	FALSE
14	B426123	13/10/2021	31/07/2023	1	FALSE
15	B831449	10/10/2018	31/07/2021	6	FALSE
16	B831449	13/10/2020	31/07/2021	6	FALSE
17	B831449	13/10/2021	31/12/2022	1	FALSE
18	B831530	09/10/2018	31/07/2021	6	FALSE
19	B831530	30/09/2019	29/04/2022	6	FALSE
20	B831530	01/03/2021	31/07/2023	1	FALSE
21	B831830	10/10/2018	31/07/2021	6	FALSE
22	B831830	13/10/2020	31/07/2021	6	FALSE
23	B831830	13/10/2021	31/12/2022	2	FALSE
24	B832070	10/10/2018	31/07/2021	6	FALSE
25	B832070	28/06/2021	31/07/2022	2	FALSE
26	B833681	27/02/2019	30/11/2021	6	FALSE
27	B833681	01/09/2022	10/03/2023	1	FALSE
28	B833744	27/02/2019	30/11/2021	2	FALSE
29	B930193	09/10/2019	29/04/2022	2	FALSE
30	B930454	09/10/2019	29/04/2022	2	FALSE
31	B930532	16/10/2019	29/04/2022	6	TRUE
32	B930532	02/08/2021	29/04/2023	6	TRUE
Master

Cell Formulas
Range	Formula
E2:E32	E2	=INDEX(SORT(FILTER($A$2:$D$100,$A$2:$A$100=A2),2,-1),1,4)=6

 

[DanteAmor]

Hi @JoshLyman.
Thanks for posting on the forum.


I show another option for versions prior to 365:

Dante Amor
	A	B	C	D	E
1	Regno	LEARNSTARTDATE	LEARNPLANENDDATE	COMPSTATUS	LATEST
2	A567883	13/10/2021	31/07/2023	1	Not
3	A671172	22/01/2020	30/09/2021	6	Not
4	A671172	23/06/2022	31/01/2023	3	Not
5	B018353	30/09/2019	30/09/2022	1	Not
6	B119158	07/10/2020	30/04/2023	1	Not
7	B216978	13/10/2021	31/07/2023	1	Not
8	B218433	05/10/2020	05/10/2023	1	Not
9	B219012	04/10/2021	04/10/2024	1	Not
10	B312726	12/10/2022	31/07/2024	6	Not
11	B312726	27/02/2023	31/07/2024	1	Not
12	B418353	07/02/2022	07/02/2025	1	Not
13	B419754	03/10/2022	03/10/2025	1	Not
14	B426123	13/10/2021	31/07/2023	1	Not
15	B831449	10/10/2018	31/07/2021	6	Not
16	B831449	13/10/2020	31/07/2021	6	Not
17	B831449	13/10/2021	31/12/2022	1	Not
18	B831530	09/10/2018	31/07/2021	6	Not
19	B831530	30/09/2019	29/04/2022	6	Not
20	B831530	01/03/2021	31/07/2023	1	Not
21	B831830	10/10/2018	31/07/2021	6	Not
22	B831830	13/10/2020	31/07/2021	6	Not
23	B831830	13/10/2021	31/12/2022	2	Not
24	B832070	10/10/2018	31/07/2021	6	Not
25	B832070	28/06/2021	31/07/2022	2	Not
26	B833681	27/02/2019	30/11/2021	6	Not
27	B833681	01/09/2022	10/03/2023	1	Not
28	B833744	27/02/2019	30/11/2021	2	Not
29	B930193	09/10/2019	29/04/2022	2	Not
30	B930454	09/10/2019	29/04/2022	2	Not
31	B930532	16/10/2019	29/04/2022	6	Not
32	B930532	02/08/2021	29/04/2023	6	Meet
Hoja1

Cell Formulas
Range	Formula
E2:E32	E2	=IF(AND(B2=LOOKUP(2,1/(($A$2:$A$32=A2)),$B$2:$B$32),D2=6),"Meet","Not")

Respectfully
Dante Amor
--------------

 

[JoshLyman]

Thanks @Fluff and @DanteAmor, they both work perfectly. Lifesavers!

 

[Fluff]

Glad we could help & thanks for the feedback.