Removing Duplicates from a Report
Hello,
I have a sheet with many line items, some of which share some columns that have duplicitous information. I would like to pull a report from that sheet to display certain information on a dashboard and, ideally, within the report, run a conditional format to remove the duplicate line items.
I am also reading up on the INDEX and COLLECT functions as it appears they may allow me to "summarize" information into another sheet which I can then run a report on for use in my dashboard. But I am having trouble understanding and implementing the INDEX and COLLECT functions.
Answers
-
I would suggest a helper column on the source sheet with a formula to flag the duplicates then a filter in the report to only show rows where the duplicates column is not flagged.
-
Hi Paul,
It looks like I have that formula working and checking a check box for any "Job Code" that is duplicated.
=IF(COUNTIF([Job Code]:[Job Code], [Job Code]@row) > 1, 1)
Although this is not getting me to where I want to be. I want my report to pull one of each of the duplicates but since they are duplicates, the formula checks the check box on all of them. Therefore, my report won't be able to distinguish against the first unique job code and its duplicates.
Thanks,
Brendan
-
Would you want the most recent or the original entry in your report?
-
Either would work, I just need to keep one entry of each duplicate.
-
Ok. In that case you can insert an auto-number column with no special formatting and modify your formula like so:
=IF([Auto-Number]@row = MIN(COLLECT([Auto-Number]:[Auto-Number], [Job Code]:[Job Code], @cell = [Job Code]@row)), 1)
-
Hi Paul,
It looks like the use of the auto numbering column and the check box column with the formula you proposed is working. To confirm, is the formula telling the check box column to not check off the highest auto number there is for that duplicate set?
Thanks,
Brendan
-
The formula is checking the box on the first row entered with that Job Code. If you wanted the most recent, you would swap the MIN function for a MAX function.
-
Got it, thanks for your help Paul!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!