This one took my by surprise as well how complicated it ended up being to get the entire workflow, but the nice thing is once you set it up it is good to! Thanks for the comment :)
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
Thank you! Smartsheet does many things very well but yes, this is not one of them and this was the easiest way I could manage to completely automate this in a logical way. However, it would be much simpler to semi-automate this (i.e., flag all duplicates and then check a box for the one[s] you want to remove, something like that). You could also use Zapier or the API for a more "simple" solution (depending on how you define simple).
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). =IF(COUNTIFS([PRIMARY REF]:[PRIMARY REF], [PRIMARY REF]@row, [DATE FIELD]:[DATE FIELD]], 1, "Duplicate", "Unique") 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 () 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.
This is really nice actually, thanks for sharing. There were a couple of minor errors I had to fix though to get the below (extra bracket and a missing @row): =IF(COUNTIFS([Primary Ref]:[Primary Ref], [Primary Ref]@row, [Date Field]:[Date Field], 1, "Duplicate", "Unique")
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
Fully automating both duplicate flagging and removal in just the core platform does require (I think) this fairly elaborate multi step solution and advanced formulas. To keep the latest data you could modify the "Remove" formula I would try some combination of MAX() and COLLECT() to query all of the duplicates and apply the checkbox to the highest RANK() within a set of duplicates. You could also use the created data in a similar manner I believe or perhaps by comparing them mathematically. A simpler but not fully automated solution might be something like using a report that groups all duplicates together and sorts them by the RANK of when they were created. Then someone could manually check the box which would be pretty obvious.
I did this by adding a ROW ID and adding a flag to the highest/MAX Row ID. When rows are added, the ROW ID increases so the most recent row has the highest Row ID.
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?
I'm assuming you are asking if you can do all of this in a single checkbox column. Possibly, it would be an extremely complex formula and might not even work. The challenge is to flag all but ONE duplicate. It's easy to do what you described (it's in this video) but it will remove all values leaving you with nothing. Also, you cannot delete rows via SS automation hence the need to move them to another Sheet as shown here.
I'm not entirely sure what you are asking. I believe you are asking if you can copy a cell formula that includes @cell within the formula reference criteria? If that is the case I would simply test a move or copy row automation and see if your formula populates in your destination Sheet. If the formula you are using can function as a column formula I would use the exact same formula in both the source and destination Sheets and it will function the same way. If this is not what you are asking feel free to reply with clarifying information so I can give a better answer.
@@jairom.lopeztapia2251 To automatically create a link between cells I'd suggest using an INDEX(MATCH()) formula or the premium application DataMesh. The best solutions will depend on what you are trying to accomplish specifically.
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.
Yes! This system is designed to work with dynamic data due to the layers of formula columns in place. These are used so that new rows will be "forced" to push through the duplicate identification and removal system. So I believe it will work as your Jira workflow pushes updates to the data so long as the values you want to evaluates are in the column being referenced by the formulas in the duplicate identification system ("Email" in the video example).
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?
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.
Or is the simplest approach to create a second helper sheet using my unique ID column to index match in the other columns I need from helper sheet 1...
@@shannonhayden6912 Yes, this should work as you described. It should not matter whether the column to evaluate for duplicates is manual or a formula. So rather than the manual "Email" column in the video you could input a formula and then evaluate the output of the formula. The duplicate system would work in the same way.
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.
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.
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!
This one took my by surprise as well how complicated it ended up being to get the entire workflow, but the nice thing is once you set it up it is good to! Thanks for the comment :)
This is where having a formal education in RDMS theory really helps to expand your vocabulary to include "aggregation".
Knowledge is power!
Legend Mate
Save my a** with this one
haha happy to hear it!
Thank you so much for this useful video.
You're welcome! Thanks for watching and the positive feedback.
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
Thank you!
Smartsheet does many things very well but yes, this is not one of them and this was the easiest way I could manage to completely automate this in a logical way. However, it would be much simpler to semi-automate this (i.e., flag all duplicates and then check a box for the one[s] you want to remove, something like that). You could also use Zapier or the API for a more "simple" solution (depending on how you define simple).
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).
=IF(COUNTIFS([PRIMARY REF]:[PRIMARY REF], [PRIMARY REF]@row, [DATE FIELD]:[DATE FIELD]], 1, "Duplicate", "Unique")
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 () 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.
This is really nice actually, thanks for sharing. There were a couple of minor errors I had to fix though to get the below (extra bracket and a missing @row):
=IF(COUNTIFS([Primary Ref]:[Primary Ref], [Primary Ref]@row, [Date Field]:[Date Field], 1, "Duplicate", "Unique")
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
Fully automating both duplicate flagging and removal in just the core platform does require (I think) this fairly elaborate multi step solution and advanced formulas. To keep the latest data you could modify the "Remove" formula I would try some combination of MAX() and COLLECT() to query all of the duplicates and apply the checkbox to the highest RANK() within a set of duplicates. You could also use the created data in a similar manner I believe or perhaps by comparing them mathematically.
A simpler but not fully automated solution might be something like using a report that groups all duplicates together and sorts them by the RANK of when they were created. Then someone could manually check the box which would be pretty obvious.
I did this by adding a ROW ID and adding a flag to the highest/MAX Row ID. When rows are added, the ROW ID increases so the most recent row has the highest Row ID.
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?
I'm assuming you are asking if you can do all of this in a single checkbox column. Possibly, it would be an extremely complex formula and might not even work. The challenge is to flag all but ONE duplicate. It's easy to do what you described (it's in this video) but it will remove all values leaving you with nothing. Also, you cannot delete rows via SS automation hence the need to move them to another Sheet as shown here.
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?
I'm not entirely sure what you are asking. I believe you are asking if you can copy a cell formula that includes @cell within the formula reference criteria? If that is the case I would simply test a move or copy row automation and see if your formula populates in your destination Sheet.
If the formula you are using can function as a column formula I would use the exact same formula in both the source and destination Sheets and it will function the same way.
If this is not what you are asking feel free to reply with clarifying information so I can give a better answer.
@@SchoolofSheets Thanks to respond. I mean a kind of link between cells from different sheets but automatically.
@@jairom.lopeztapia2251 To automatically create a link between cells I'd suggest using an INDEX(MATCH()) formula or the premium application DataMesh. The best solutions will depend on what you are trying to accomplish specifically.
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.
Yes! This system is designed to work with dynamic data due to the layers of formula columns in place. These are used so that new rows will be "forced" to push through the duplicate identification and removal system. So I believe it will work as your Jira workflow pushes updates to the data so long as the values you want to evaluates are in the column being referenced by the formulas in the duplicate identification system ("Email" in the video example).
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?
This is shown starting at the ~18 minute mark!
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.
Or is the simplest approach to create a second helper sheet using my unique ID column to index match in the other columns I need from helper sheet 1...
@@shannonhayden6912 Yes, this should work as you described. It should not matter whether the column to evaluate for duplicates is manual or a formula. So rather than the manual "Email" column in the video you could input a formula and then evaluate the output of the formula. The duplicate system would work in the same way.
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.
I worked with serial numbers and the formula is not working. It gives me only 0 even there is a duplicate.
I would suggest copying the formula in the video exactly, including column names, and then adjusting your column names after!
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.