Compare submissions
![mrodri90](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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?
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
Hey @mrodri90
Yes, that formula should work! 🙂
Can you post a screen capture of how you have it in your sheet?
Join us at Smartsheet ENGAGE 2024 🎉
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))
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives