Have two auto-number or rank by created date?

Okay, so I have been working this problem for a few days and it's officially making me crazy. Here's the problem statement:

I have 3 intake sheets for different business units. Each has an Auto-Number column that has a prefix for the BU.

I need to consolidate all of the attachments from those 3 sheets into 1 sheet. So on each sheet, I have an automation. When an attachment is added, copy to "Attachments" Sheet.

However, when multiple attachments are added to Row BU102, it gets copied over multiple times.

I would like to write an automation to remove (move to archive) the duplicate lines, but need to keep the latest ones.

I need to retain the ability for people using this sheet to sort by things other than the created by date.

Solution 1: The first solution I tried was using RankEQ to isolate which was the newest based on the created date. The issue was that RankEQ can't read in dates, and the equations that you can use to make it work, erase the timestamp which is a problem since these come in back to back.

Solution 2: I was able to get it working by creating helper columns. I created a new auto-number column (New Row ID) (just numbers 1-100) and then a Rank helper column =RANKEQ([New Row ID]@row , COLLECT([New Row ID]:[New Row ID], [Row ID]:[Row ID], [Row ID]@row )) and a Validation helper column =IF(Rank@row < COUNTIF([Row ID]:[Row ID], [Row ID]@row ), "Delete", "Keep"). However, to get the New ID column, I had to change the format of the "ROW ID" column to text which broke all of my copy automations.

Any ideas here?

PS. I know I can't just write an equation to auto-number because when people sort by things other than the created by, it will break, unless there is something that I am not thinking of.

Any ideas are appreciated.

Find more posts tagged with

Formulas

Automations

ranks

Auto numbering

copy row

Accepted answers

All comments

gilmoregirl007

I messed around long enough to get it to rank by created date, but because many are created in the same minute, it does not work since both show the same rank.

I also thought I could avoid that problem by just setting the automations to only run hourly, but that is not an option. Copy Rows apparently must run "when triggered"

amarkumar

https://community.smartsheet.com/discussion/144375/have-two-auto-number-or-rank-by-created-date

@gilmoregirl007

Thanks for clearly explaining your challenge! This is a common issue when consolidating data from multiple Smartsheet intake sheets, especially when automations cause duplicate rows as files are processed. Here’s a straightforward, copy-paste answer without formatting or beautification:

Smartsheet formulas are limited for this use case because RANKEQ and COUNTIF work best with simple numeric data, and the row auto-numbering system isn’t stable when users sort the sheet or import new data. The underlying problem is that Smartsheet does not provide row-level timestamps you can reliably sort with formulas, and its formulas can’t truly “see” row order based on date if users re-sort or filter. You also cannot use formulas to reference true Smartsheet Row IDs or use formulas to directly manipulate or deduplicate attachments.

Here are some approaches to work around this, specific to Smartsheet limitations:

Use a helper Created (System Date/Time) column. This is the only timestamp that is stable when users sort differently. Filter or create reports using this, not formulas, to surface the latest.
Instead of depending on formulas to mark duplicates, use Data Shuttle or Bridge to process and dedupe the sheet outside of Smartsheet, then write back only unique rows based on your Row ID, keeping the one with the most recent Created date.
If you must stay inside Smartsheet, set up an automated workflow (not formula-based) that moves rows to Archive when [Row ID] + [Created] matches an older version (may require a helper workflow or Bridge, since formula-only approach breaks if order changes).
If you want a semi-automated method, add a Checkbox (“Duplicate?”) and manually use filters to find older Created dates for each Row ID, then bulk-move marked rows to Archive as needed.
There’s currently no built-in formula or column type in Smartsheet that can maintain an auto-incrementing, persistent row order unaffected by sort, filter, or other user actions.

Summary: To reliably deduplicate and keep only the latest for each Row ID in Smartsheet, either:

Use the System Created Date and a manual/filtering or reporting approach and archive in batches.
Use an external tool like Data Shuttle or Bridge for scheduled deduplication logic, pulling Created and Row ID, keeping the most recent row for each Row ID, and archiving older ones.
There is no formula-only solution that works reliably if users sort or modify the sheet outside of your set process, because auto-numbering and row formulas will break under those scenarios.

TVang

Ensure that your auto-number column contains numbers only.

Create a helper column like "myHelper".

[myHelper] = COUNTIFS([Business Unit]:[Business Unit], [Business Unit]@row , AutoNum:AutoNum, >AutoNum@row )

The value in [myHelper] will be "0" for the most recent row.

Create an automation workflow that runs at the end of the day to archive rows (move to another sheet) where [myHelper] is not equal to "0".

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.