# Compare submissions

✭✭
edited 06/27/23

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?

Tags:

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭

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.

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭
edited 07/05/23

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.

• Employee

Hey @mrodri90

Yes, that formula should work! 🙂

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

October 8 - 10, Seattle, WA | Register now

• ✭✭

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

• Employee

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")