Large/Index to return value once only
Please find example of the issue that I am experiencing in a Smartsheet column. As you can see in the screenshot, there are duplicate values in 9 & 11, then 12, 13 & 14. However, I wish to only populate the first value i.e. 9 & 12 and leave the others blank. In other words, I only want the latest/highest to populate with the ID.
This is the current formula: =IF([Cumulative Cost 1]@row = MAX(MAX(COLLECT([Cumulative Cost 1]:[Cumulative Cost 1], [ID 1]:[ID 1], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 2]:[Cumulative Cost 2], [ID 2]:[ID 2], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 3]:[Cumulative Cost 3], [ID 3]:[ID 3], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 4]:[Cumulative Cost 4], [ID 4]:[ID 4], [ID 1]@row))), [ID 1]@row)
I have experimented with Index and large but to no avail. Any insight would be much appreciated.
Answers

Hi @CAH
How are you identifying what the "latest/highest" is in your sheet? Is this based on row number (e.g. row 9 is more recent than row 10 based on the row number) or is this based on date? (Row 9 was inserted more recently based on the created date from row 10).
You could add an AND statement to your IF formula to say return that value but only if it meets your MAX criteria AND if the current row also meets a MIN criteria (minimum row number) or a different MAX criteria (if you're looking at date).
For example:
=IF(AND(Created@row = MAX(COLLECT(Created:Created, [ID 1]:[ID 1], [ID 1]@row, [Cumulative Cost 1]:[Cumulative Cost 1], [Cumulative Cost 1]@row)), [Cumulative Cost 1]@row = MAX(MAX(COLLECT([Cumulative Cost 1]:[Cumulative Cost 1], [ID 1]:[ID 1], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 2]:[Cumulative Cost 2], [ID 2]:[ID 2], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 3]:[Cumulative Cost 3], [ID 3]:[ID 3], [ID 1]@row)), MAX(COLLECT([Cumulative Cost 4]:[Cumulative Cost 4], [ID 4]:[ID 4], [ID 1]@row)))), [ID 1]@row)
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hello Genevieve,
Thanks for your help. It doesn't matter which really, so long as it only displays once.
However, I have input your formula but it still hasn't changed the results. I have then noticed that because the user copies rows down in bulk to set up, they have the same created date, therefore, I think we would need to adapt based upon row number/index. Would you be able to assist with a solution for this? The second scenario that I want to avoid is that the rows may be regularly sorted, therefore, if the user has already signed off an extra cost and the 'latest ID' shifts to another row, the user will then have to reapprove.
Many thanks,

Hey @CAH
Do you have anything on the row that determines which one should not be seen as a duplicate?
If the created date and time is the exact same, and the row order might be changed, how do you know which one of those 3 rows is the one you want to keep as the "latest" row?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!