Compare submissions

Hello!

I would like to compare submission such that older submissions from same field "Name" are moved to different sheet. I can add a helper column called "Older" and would like to either populate the older rows with the word "Older," and number to signify it is older, or a simple yes/no dropdown. I currently have a submission date column. Thank you.

Anyone have an idea of what a formula for this would look like?

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Genevieve P.

Hi @mrodri90

You can use the MAX function looking at your Date column to identify if a date for that "Name" is the maximum date (newest date) or not.

If it IS the newest (or only) row with that Name, then leave it blank. If it's NOT the newest, then you can return "Older".

Try a formula like this:

=IF(Date@row = MAX(COLLECT(Date:Date, Name:Name, Name@row)), "", "Older")

Cheers,

Genevieve

mrodri90

Hi @Genevieve Choquette !

Thank you for the assistance. I think I did not phrase the question correctly. It would be the MAX for each unique person. For Instance, the same person submitting multiple times. I asked the same question in a different post because it was related and I got a solution.

Genevieve P.

Hey @mrodri90

I'm glad you found a solution! 🙂

The formula above is specific to each unique Name - in my screen capture you can see that there are 2 Genevieve rows, and one is marked Older. Then 2 Antonie rows, with one marked Older. The one Joe row is the only row with Joe, so it defaults to being the newest row.

Cheers,

Genevieve

mrodri90

Hi @Genevieve P. Could I use the same formula except substitute the MAX for a MIN to select the newest entry tied to the NAME field? Something like

=IF(Date@row = MIN(COLLECT(Date:Date, Name:Name, Name@row)), "", "Newest")

I just tried it, and I keep getting Newest populating the cell for every submission regardless of Created Date. I tried text/number and dropdown option.

Genevieve P.

Hey @mrodri90

Yes, that formula should work! 🙂

Can you post a screen capture of how you have it in your sheet?

mrodri90

This formula below adds "Newest" to all rows. It did add "Old" to one row. As I delete the "Old" row, a new "Old" is labeled. Is there a way to have the formula select the single and newest submission for a particular FullName?

=IF(CreatedDate@row = MIN(COLLECT(CreatedDate:CreatedDate, FullName:FullName, FullName@row)), "Old", "Newest")

So I have another column that works well called Older, but it would be nice to just look for one cell that says Newest. As opposed to looking for blanks. The blank is the newest. That formula is

=IF(FullName@row <> "", IF(COUNTIFS(FullName:FullName, FullName@row, CreatedDate:CreatedDate, @cell > CreatedDate@row) > 0, 1))

Genevieve P.

Hi @mrodri90

My apologies! I needed a bit more coffee today, haha.

Your original formula is actually looking for the Newest one already. What it's doing is looking for the MAX date, the newest date, and returns blank if it IS the Newest. Otherwise, return Oldest.

Instead of changing MAX to MIN, we just need to change the value if True portion of the formula and add in the word "Newest".

=IF(Date@row = MAX(COLLECT(Date:Date, Name:Name, Name@row)), "Newest", "Older")

so in your case:

=IF(CreatedDate@row = MAX(COLLECT(CreatedDate:CreatedDate, FullName:FullName, FullName@row)), "Newest", "Old")

Cheers,

Genevieve

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.