Work around for duplicates to only show the most updated row

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!

Find more posts tagged with

Sheets

Formulas

Reports

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.