Finding Duplicate rows then comparing Create Dates
Hi All,
I'm trying to develop a simple system for people to create new Smartsheet rows with a form with two fields.
Order Number
Location
An order can move from location to location in our facility so the idea is the user who moves the order completes the form with the two fields and submits
I want to be able to find duplicate order numbers (which i have found the solution with a checkbox)...
=IF(COUNTIF([Order Number]:[Order Number], [Order Number]1) > 1, 1)
and then compare the two CREATED ON dates.. then archive the oldest one to another sheet.
Essentially I'd like to automate the archive/deleting of the oldest row.
I'm having trouble figuring out a way to compare the dates on the found duplicated rows.
Thanks for any suggestions
Clay
Best Answer
-
You would replace [Created On] with the column name of where your created dates are and [Order Number] with whatever column your order numbers are in.
Answers
-
We will start with a COLLECT function to pull all dates from the [Created On] column based on the data in the [Order Number] column matching.
COLLECT([Created On]:[Created On], [Order Number]:[Order Number], [Order Number]@row)
Now that we have collected all of the dates, we need the most recent one.
MAX(COLLECT([Created On]:[Created On], [Order Number]:[Order Number], [Order Number]@row))
Now that we have the most recent one, we use an IF formula to say that if the date in the [Created Column] does not match the most recent date out of all listed for that [Order Number], we want to check a box.
=IF([Created On]@row <> MAX(COLLECT([Created On]:[Created On], [Order Number]:[Order Number], [Order Number]@row)), 1)
Then you can set up your Move Row Automation to run when that box is checked.
-
Hi Paul
Thanks so much for the reply. I'm not very familiar with how to use the COLLECT function.. I'm not sure which column to use it on.
Thanks
Clay
-
You would replace [Created On] with the column name of where your created dates are and [Order Number] with whatever column your order numbers are in.
-
Thank you so much! I have spent 6 hours trying to work this out... Brilliant solution.
-
Happy to help. 👍️
-
Hi @Paul Newcome,
In my version I'm comparing Accounts and Created (shows date and time row was created). I tried this solution and it checks all the boxes with past dates per Account. Is there a way to get this to look at each date in the Created column individually, find where Accounts are duplicated for each date, compare the Created for the duplicates and check the box in the rows that do not have the latest Created between the duplicates?
Thanks for any suggestions
-
@BSS Are you able to provide a screenshot for reference?
-
Hi @Paul Newcome ,
For example:
In the screenshot above, all past dates for each account are checked (the formula used is below). However, I would like the output to consider each date individually and only check the box if it is not the most recent entry each day.
=IF(Created@row <> MAX(COLLECT(Created:Created, Account:Account, Account@row)), 1)
Example output I am hoping to have:
Here, the original entry is checked because it is the oldest entry for that account on that specific day. I only would like to keep the most recent entry per account per day.
I hope I am explaining this properly.
Thank you for your quick response!
-
Hi there -
I think this should work:
=IF(Created@row < MAX(COLLECT(Created:Created, Created:Created, DATEONLY(Created@row), Account:Account, Account@row)), 1)Note that you need to be a bit careful when using the Created column because it is based on GMT. So even though two rows are created on the same day in your timezone, they may be created on two different days when measured in GMT. This might create undesired results when determining duplicates.
-
Hi @SJ Sellers ,
That worked. Thank you so much for your assistance and insight!
-
Hello @Paul Newcome
I've tried that formula you described but I got an error, #INVALID COLUMN VALUE
any idea of the issue?
Created On is a date only field (tried also the original Date/Time) and my "OrderNumber" is an Auto Number column type.
****
Solved the issue in the meantime :)
for those trying, the COLLECT function only works within other functions, in this case, IF & MAX. Thanks!
