How to filter/lookup rows of data based on the latest date in a cell

[Monty85]

I have a large set of a data that has multiple entries for the same users based on start dates. I'm trying to find a way to either filter or extract the data to a new sheet where it will show the latest row only for each user.

I think I can extract a single cell based on the criteria, but is there a quick way to show just the entre row with the latest date for each user?

All the latest dates are different as well.

Example:

USER_ID	NAME	DOB	START_DATE	END_DATE
123456	Tiger Woods	30/12/1975	01/12/2019	31/12/2019
123456	Tiger Woods	30/12/1975	01/07/2019	30/06/2020
456456	Adam Scott	16/07/1980	01/07/2021	30/06/2022
456456	Adam Scott	16/07/1980	01/07/2022
987654	John Daly	28/04/1966	01/07/2020	30/06/2021
987654	John Daly	28/04/1966	01/07/2021	31/12/2021
987654	John Daly	28/04/1966	01/01/2022

And the result would be:

USER_ID	NAME	DOB	START_DATE	END_DATE
123456	Tiger Woods	30/12/1975	01/07/2019	30/06/2020
456456	Adam Scott	16/07/1980	01/07/2022
987654	John Daly	28/04/1966	01/01/2022

Thanks,
Monty

 

[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’)

latest date for each user

Latest date using which column?

 

[Monty85]

Thanks, a/c details updated.

 

[Monty85]

Latest date using which column?

Start Date

 

[Peter_SSs]

Thanks, a/c details updated.

Thanks.

Start Date

In that case, why is Tiger Woods expected result start date 01/07/2019 when another of his rows has the later start date of 01/12/2019?

 

[Monty85]

Thanks.


In that case, why is Tiger Woods expected result start date 01/07/2019 when another of his rows has the later start date of 01/12/2019?

Ah, because im a bonehead and can't type my own examples properly.

Sorry about that - I dont think i can edit my post, but yes it should just be latest row for him which would be the first one - start = 01/12/2019.

 

[Peter_SSs]

See how this goes then

Monty85.xlsm
	A	B	C	D	E
1	USER_ID	NAME	DOB	START_DATE	END_DATE
2	123456	Tiger Woods	30/12/1975	1/12/2019	31/12/2019
3	123456	Tiger Woods	30/12/1975	1/07/2019	30/06/2020
4	456456	Adam Scott	16/07/1980	1/07/2021	30/06/2022
5	456456	Adam Scott	16/07/1980	1/07/2022
6	987654	John Daly	28/04/1966	1/07/2020	30/06/2021
7	987654	John Daly	28/04/1966	1/07/2021	31/12/2021
8	987654	John Daly	28/04/1966	1/01/2022
Sheet1

Monty85.xlsm
	A	B	C	D	E
1	USER_ID	NAME	DOB	START_DATE	END_DATE
2	123456	Tiger Woods	30/12/1975	1/12/2019	31/12/2019
3	456456	Adam Scott	16/07/1980	1/07/2022
4	987654	John Daly	28/04/1966	1/01/2022
5
Sheet2

Cell Formulas
Range	Formula
A2:E4	A2	=LET(f,FILTER(Sheet1!A2:E8,Sheet1!D2:D8=MAXIFS(Sheet1!D2:D8,Sheet1!A2:A8,Sheet1!A2:A8)),IF(ISNUMBER(f),f,f&""))
Dynamic array formulas.

 

[Monty85]

See how this goes then

Thanks, first impression looks like this is working - lots of data to check though but fingers crossed this has done the trick.

Thanks again.

 

[Peter_SSs]

You're welcome.
Let us know (with small sample again) if any issues arise.