Can I used conditional formatting to high duplicates in reports?

Austen

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!

Lauren Dominique

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

Lauren Dominique

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

Austen

I think this would work great! Thank you.