Work around for duplicates to only show the most updated row

Christina09

Hi,

I have a list of names that were entered to the sheet more than once (but a different value in other columns).

I have a helper column to identify which are the duplicates, but is there a way to show only one of the updated one in a report?

Source Sheet

Test 1 & Test 2 are duplicates, but I only need the newer enter to show up in a report.

Is there a way to work around it?

Thank you for your help!

Maaik Meijerink

Hello Christina,

If you mean only the newer based on the Start Date, of course you can: you can use a column formula based on the MAX of the start date and the range of the specific Name via the COLLECT formula.

=IF(MAX(COLLECT([Start Date]:[Start Date]; Name:Name; Name@row)) = [Start Date]@row; 1)

And of course in your report filter on the flagged ones.

Please also check the screenshot.

Grtz. Maaik

Christina09

@Maaik Meijerink

Thanks for your quick response. This works, but if I want to do the same thing but in a column with ID#, the one that started with 0's, appears to be #INVALID OPERATION. is there a formula that works both for number and text?

Thank you so much! :)

mragans23

Hi Christina, I would love to know how you was able to get checkbox column to work as a formula, thanks.

Christina09

@mragans23

I added a helper column for ID# since the formula won't match if it's a number. =VALUE([ID#]@row)

=IF(MAX(COLLECT([Start Date]:[Start Date]; [ID# Helper]:[ID# Helper]; [ID#]@row)) = [Start Date]@row; 1)

Hope that helps.