👋 Welcome! Introduce yourself and connect with your peers in Government to receive your industry badge.
Can I used conditional formatting to high duplicates in reports?
We have several grant programs within our organization and I am working on funneling all the form submissions from each into a main report. I would like that report to identify duplicate submissions so I can consolidate and make sure candidates aren't overwhelmed by having too many cases open. Can I use the conditional formatting to cross reference Last name and DOB to highlight a duplicate? Or is there another solution? Thanks!
Best Answer
-
Hi Austen!
You will need to add some helper columns and use formulas to accomplish this. Here's a step-by-step:
First, create a helper column called "Unique ID" (or something like that) to serve as the field you will use to determine if something is a duplicate record or not. Since you want to reference both Last Name and DOB to determine if a record is duplicative of another, you'll enter this formula into the Unique ID column: =[Last Name]@row + " " + DOB@row
Second, create a second helper column called "Duplicate Tracker". This will be the field that flags if a value is duplicate or not. The formula for this column will be: =IF([Unique ID]@row = "", "", IF(COUNTIF([Unique ID]:[Unique ID], =[Unique ID]@row) > 1, "YES"))
Basically, this formula is looking to see if there is more than 1 of [Unique ID]@row anywhere in the [Unique ID] column. If there is more than 1, the formula produces a "YES" to flag it as a duplicate. The first part of that formula reads for blanks because, without it, it would flag two blank cells as duplicates.
Once those fields are in place, you can then use conditional formatting so when the Duplicate Tracker column contains "YES", the entire row gets highlighted.
An example of what all of this would ultimately look like in your sheet/report is below:
I hope this is a helpful start! Let me know if you still have questions or continue to run into any issues.
Best,
Lauren
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
Answers
-
Hi Austen!
You will need to add some helper columns and use formulas to accomplish this. Here's a step-by-step:
First, create a helper column called "Unique ID" (or something like that) to serve as the field you will use to determine if something is a duplicate record or not. Since you want to reference both Last Name and DOB to determine if a record is duplicative of another, you'll enter this formula into the Unique ID column: =[Last Name]@row + " " + DOB@row
Second, create a second helper column called "Duplicate Tracker". This will be the field that flags if a value is duplicate or not. The formula for this column will be: =IF([Unique ID]@row = "", "", IF(COUNTIF([Unique ID]:[Unique ID], =[Unique ID]@row) > 1, "YES"))
Basically, this formula is looking to see if there is more than 1 of [Unique ID]@row anywhere in the [Unique ID] column. If there is more than 1, the formula produces a "YES" to flag it as a duplicate. The first part of that formula reads for blanks because, without it, it would flag two blank cells as duplicates.
Once those fields are in place, you can then use conditional formatting so when the Duplicate Tracker column contains "YES", the entire row gets highlighted.
An example of what all of this would ultimately look like in your sheet/report is below:
I hope this is a helpful start! Let me know if you still have questions or continue to run into any issues.
Best,
Lauren
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
-
I think this would work great! Thank you.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives