Formula to determine Duplicate items copied from one sheet to another
I have multiple sheets feeding this sheet. When items are modified changed updated or added they get copied to this master sheet. The newest items are the ones I have highlighted in yellow which I want to identify as the "Latest", that way I can have it auto move the out dated row to an archive sheet.
This is the current formula I am using but I cant seem to get it to work just right.
=IF(COUNTIFS([DevOps ID]:[DevOps ID], [DevOps ID]@row, [DevOps ID]:[DevOps ID], [DevOps ID]@row) > 1, IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [DevOps ID]:[DevOps ID], [DevOps ID]@row, [DevOps ID]:[DevOps ID], [DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")
Thank you,
Best Answers
-
Since your auto-number column has text, you will need to insert a text/number column (called "Row" in this example) and use this column formula:
=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
Then your COUNTIFS would use this in the MAX/COLLECT piece in place of the created date.
-
=IF(COUNTIFS([Helper DevOps ID]:[Helper DevOps ID], @cell = [Helper DevOps ID]@row) > 1, IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Helper DevOps ID]:[Helper DevOps ID], @cell = [Helper DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")
Fixed, Thank you for all your help.
Answers
-
In your COUNTIFS() statement and then again in your COLLECT() statement, you are comparing the [DevOpsID] column to [DevOps ID]@row twice. Is this a bad copy/paste? You are essentially saying, (If A = B and if A = B).
-
I removed the duplicate line, so now the formula reads "=IF(COUNTIFS([DevOps ID]:[DevOps ID], [DevOps ID]@row) > 1, IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [DevOps ID]:[DevOps ID], [DevOps ID]@row, [DevOps ID]:[DevOps ID], [DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")"
The line had no impact on the formula.
-
It looks like it should be working. Are you getting an error, or is it checking the wrong boxes?
-
If your intent is to identify only the single most recent entry for each [DevOps ID], you will need more information. It looks like you are currently comparing each entry to the date in [Created Date], but in your screenshot, all entries have the same date. I am not sure of the source of [Row ID], but there are two inconsistent entries there, so it would likely not work. The other option would be to add an autonumber column. You could then look for the highest number in that column among rows that match the current [DevOps ID].
-
No, the formula should only label the yellow rows as "Latest", the others should either read as out-dated or N/A.
-
Yes, that is the intent. The created date is the date that the information was copied over to the current sheet, so when the rows are updated they get copied over again with the new current date. the Row ID is an auto number is now fixed.
-
Since your auto-number column has text, you will need to insert a text/number column (called "Row" in this example) and use this column formula:
=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)
Then your COUNTIFS would use this in the MAX/COLLECT piece in place of the created date.
-
That Seemed to work. I just need to see why I am getting values falling into the N/A category.
Posting the latest formula here.
=IF(COUNTIFS([DevOps ID]:[DevOps ID], [DevOps ID]@row) > 1, IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [DevOps ID]:[DevOps ID], [DevOps ID]@row, [DevOps ID]:[DevOps ID], [DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")
Thank you for all the help.
-
What happens if you remove that blank row?
-
Nothing changes, for some reason the blank row continues to get added when the rows get batched over. I may have a hidden row on one of the feeder sheets.
-
Ok. Lets try this:
Insert a text/number column and use this column formula:
=[DevOps ID]@row + ""
(plus quote quote)
This will ensure that every value in that column is a text value so that we have consistent data formatting. Then reference this helper column in your formula instead of the original.
We will also use @cell references like so:
=IF(COUNTIFS([Helper DevOps ID]:[Helper DevOps ID], @cell = [HelperDevOps ID]@row) > 1, IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Helper DevOps ID]:[Helper DevOps ID], @cell = [DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")
-
=IF(COUNTIFS([Helper DevOps ID]:[Helper DevOps ID], @cell = [Helper DevOps ID]@row) > 1, IF([Row #]@row = MAX(COLLECT([Row #]:[Row #], [Helper DevOps ID]:[Helper DevOps ID], @cell = [Helper DevOps ID]@row)), "Latest", "Out-Dated"), "N/A")
Fixed, Thank you for all your help.
-
Happy to help. 👍️
-
I have a similar issue as the op. I have multiple sheets feeding one sheet.
A board I found before suggested to use rankeq function. So that is the approach I had and the issue I am running into is my column formulas aren't working correctly.
Rank Column Formula = =IFERROR(RANKEQ([Job#]@row, COLLECT([Job#]:[Job#], [Name of Marketing Project]:[Name of Marketing Project], [Name of Marketing Project]@row, Duplicate:Duplicate, 1), 1), "-")
Remove Column Formula = =IF(Rank@row > 1, 1, IF(Rank@row = "-", 0))
The rows that are supposed to be removed (in the remove column row) are not marked as remove at times. I believe its because of the dashmarks I have in my Rank Column formula for the "IfError". The second screenshot shows it working correctly for that specific row.
Any ideas? Let me know if I need to include more details. Or if the approach given above works better we can try that, but I got confused in this thread with the formulas
-
@mbsamuel6 I have never used the RANKQ for flagging duplicates as I have seen some issues with it. I have never had a problem with the method I us though, but that method can vary a little bit depending on your exact needs.
What exactly is it you are trying to accomplish?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
