Help

Add number if there are duplicates

Topic Labels: Formulas
372 2
cancel
Showing results for 
Search instead for 
Did you mean: 
MrZeller
4 - Data Explorer
4 - Data Explorer

Hi everyone!

I'd need help creating a formula that adds a number if there is one or more duplicate of that record.

Example: if i have two record 'maria', one should be 'maria-1' and the other 'maria-2' or similar.

For now i've tried a workaround using autonumbe, making a formula {name} & {number} but so they're not numbered sequentially by duplicates, just by the table order.

Any ideas?

2 Replies 2

Airtable formulas generally aren't "aware" of records other than the one it's currently operating on – and then, because formulas have no access to "record order", you can't really have it assign a number that increments for each additional matching record.

If the autonumber doesn't suit your purposes and you want sequential numbering of duplicates, you would need a script, either one that runs on demand or via automation.

You can do it manually. Example

Alexey_Gusev_0-1693038438634.png

add autonumber. add column right to your field where you search duplicates, type "link to a table (select new)"

Alexey_Gusev_1-1693038584647.png

 

copy-paste your field there.
Now go to New Table and create such rollup

Alexey_Gusev_2-1693039538294.png

space after semicolon is mandatory
now return to your table and throw this rollup via lookup here (i renamed it to just 'rollup' to soften the formula)

Alexey_Gusev_3-1693039770081.png

and now craft formula that finds 'index' of current element ID in the whole list of similar elements, then counts elements of the left side of it (by substituting semicolon, for example)

Alexey_Gusev_4-1693039986470.pngAlexey_Gusev_5-1693040080763.png

 

 


I hope you understand how it works and what to do next ))
It might look hard and crazy, but indeed it's not, when you do it several times.
In Airtable, record 'doesn't know about each other', so to operate totals and other summaries, you need to create a pivot table (method with link by copy-pasting) and perform rollup/lookup/count operations between these two tables.