Specific filter to remove rows based on comparison between each other

[AtOfficeNeedHelp]

Hi all,

I am in a situation where the data I export from mongo is impossible to filter based on the very latest recorded input.

I attached a sample file of how the data would look like.

I therefore have a situation where:

ID 1234 has three rows, each with a different input value and a different change date. Only the row with the most recent change date compared to current date is relevant and needed. I therefore need either:

1) to have all other rows removed and only the one with the most recent change date kept;
2) have the row with the most recent change date created on a different sheet.

In my mind the explanation of the process would be :

1) go through each row to identify those with the same ID;
2) between those with the same ID, check the change date field value and only keep the one which, compared to all other rows with the same ID, has the most recent changedate compare to current date.

I understand this might not be easy but I'm open to any solution, if any help could be provided please.

[FlameRetired]

Hello AtOfficeNeedHelp. Welcome to the forum.

In the attached find in Sheet1 where I have copied all the IDs and applied the remove duplicates feature and today's date in E1.
Then in cell B2 this formula copied down and across.

Please Login or Register  to view this content.

P.S. you will probably need to reformat for dates.

Yields this:

	A	B	C	D	E
1	ID	Changedate	Input	Today's date →	12/9/2022
2	1234	12/1/2022	65
3	abcd	1/1/2022	12

Does this do what you want?

[AtOfficeNeedHelp]

Think this is it, will check it out thank you!