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

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?
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!