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.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

CAH

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 re-approve.

Many thanks,

Genevieve P.

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?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.