Removing Duplicate Values from a Smartsheet Database | Smartsheet Tutorial

In this video, Dan from School of Sheets shows us how to remove duplicate values from a Smartsheet database using formulas and automated workflows. Dan also shows us how to use Conditional Formatting to help us identify duplicate data quicker.

💻 FUNCTIONS USED
➣ JOIN(COLLECT())
➣ RANKEQ()
➣ IF()

🏢 ABOUT SCHOOL OF SHEETS
➔ We are a bespoke consulting firm specializing in developing custom Smartsheet Solutions
➔ We are a Smartsheet Aligned Gold partner with a team of certified consultants and software developers.
➔ We work with businesses of all sizes and industries.

🤝 WANT TO WORK WITH US?

📚 KEY RESOURCES

🔔 SUBSCRIBE FOR MORE GREAT CONTENT

⌚ TIMESTAMPS
00:00 – Intro
00:05 – Question from the Smartsheet Community
00:45 – Setting Up Sample Data
01:05 – Section 1: Creating Formulas to Count Duplicates and Check Boxes
07:55 – Section 2: Capture Unique Data Points (IDs) Only Associated with Duplicate Values
10:45 – Section 3: Use Logical Formulas to Rank Duplicates and Isolate All But One
14:15 – Section 4: Set Up Automated Workflow to Remove Identified Duplicates
17:30 – Summary and Alts to the Formula
18:35 – Outro

#smartsheet #formula #removeduplicates

[JessicaMeyer-vz]

This was a tricky one - I scoured the internet for this solution and finally found it in this video. Thank you Dan & School of Sheets!

[rooboyroo]

Legend Mate

Save my a** with this one

[LadyTMarie]

Thank you so much for this useful video.

[HOWYOUDOIN]

This is where having a formal education in RDMS theory really helps to expand your vocabulary to include "aggregation".

[cavanagd]

Loved the video and your way of explaining things. It's a cool way to explore the use of some formulas as well. However, I find it hard to believe that there isn't a less labour intensive way to do this? If so this doesn't speak very highly of smartsheet

[genesiasheree]

Can you just create a colum that includes checkmark to reflect what row has been duplicated and I'm assuming that's a formula and once that has been generated then can you just create a automation that's delete row or move row to another sheet?

[tiagoandrade]

Is there a simpler way to create a similar process to this but instead of keeping the first occurrence data, you keep the latest data? (maybe by using the created date?) Thank you

[MariaPatton-gqyr]

Will this work with a "dynamic" database? We have created a Jira Workflow that pulls a list of stories and their corresponding epics into a sheet. This sheet updates any time there are changes (stories are added/removed, etc) in Jira, thus the de-duped list would have to change as stories are added/removed.

[jairom.lopeztapia]

I´ll be looking for this all my time in job. Thanks! Now, how can i copy a @cell from a sheet in another automatically?

[shannonhayden]

Will this approach work if my column with duplicates is a formula looking up to another table? I need a way to pull live data from a base sheet into my own helper sheet (columns 1, 2, 3), concatenate rows into my own unique ID code (column 4), and then delete duplicates based on the concatenate row (column 4), without turning my own table into static values.

[christinecao]

I followed step by step and everything (formulas) all worked until I ran the workflow to "Move" the rows where "REMOVE" checkbox was checked. The workflow wiped out/cleared the entire sheet including all the formulas I created following your instructions. Any reason why? it moved EVERYTHING over, even those rows where the REMOVE checkbox was NOT checked.

[mike_mishelle]

This is close to what I need to do. However I'm tracking mileage of vehicles, so I need to keep the latest entry instead of the first one. Any suggestions?

[longmatey]

IF YOU HAVE DATES IN YOUR SHEET (if not create dates using automation) - the following will allow you to pull duplicated information in a single column (no additional helpers outside date column).


The way this is setup - it will only record the duplicate entries for the OLDEST primary entry. To change that to show duplicates for the NEWEST, flip the LESS THAN (<) to GREATER THAN (>) in the line below.
PRIMARY REF = your primary reference
DATE Field = a date - any format seems to work (This can also be a ranking field as shown in the video, dates just make mores sense at times).
If it is a duplicate and the oldest duplicate - it will post as a duplicate. If it is not a duplicate or not the oldest duplicate - it will post as unique.

Thank me later i guess.

[laderasmcadam]

I worked with serial numbers and the formula is not working. It gives me only 0 even there is a duplicate.

[kyle__]

OMG, why can't Smartsheet simply provide a "remove duplicate" command like the Excel does? As Excel had such function for over a decade. This is so dumb! If it would take this much time just to rid of duplicates, Smartsheet should be re-named as Dumbsheet; and we ought to threw this crap out of window altogether.