nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

Conditional Formatting Duplicates

bhope51

Hello,

Is there a way to conditionally format duplicates if a value is in a column and if a box is checked? I need this to look through the whole sheet where there are many input values by the customer via a form.

Thank you.

Brian

Find more posts tagged with

Support

Conditional Formatting

Project Management

Comments

Chris McKay

Hi Brian,

No way to natively locate duplicates and apply conditional formatting, however, you can cheat.

Assuming you have a value column (we'll call it Name) and checkbox column (let's call this one Booked), we can create a Duplicate checkbox column to the right and enter the following formula, copying it all the way down as far as you need:

=IF(AND(COUNTIFS(Name:Name, Name1, Booked:Booked, 1) > 1, Booked1), 1, 0)

You can then use conditional formatting to highlight rows/cells where Duplicate is checked and hide the Duplicate column when you are satisfied with the results.

bhope51

Hi Chris,

This is helpful, but I have one question. will the formula you listed above only look for Joe Bloggs? you have it listed in the countifs for "Booked 1". What I am looking for is an example where the duplicates box checks if any name is repeated in the name column with the condition that the booked column has been checked.

Basically all the Fred Nerks will have the duplicate checked since there is one Fred Nerks where the booked column has been checked.

We are looking to create a validation check in our sheet for accounts that have been used in the past for billing. New accounts will highlight to not the account needs to be confirmed.

Brian

Chris McKay

Hi Brian,

No , the formula is not dependent on any particular value in the Name column.

To break it down, the formula is calculating the count of values that match both these criteria:

the value in Name1 (i.e. the same row the formula is on) occurs more than once in the Name column (i.e. it's a duplicate name)
the Booked value is checked (i.e. it's a duplicate booking)

If both criteria are fulfilled (i.e. you have a value that occurs more than once in the Name column with the corresponding Booked checkbox ticked), then the Duplicate cell (where the formula lives) is populated with a 1.

If there are 1000 Fred Nerks, but only one row has the Booked checkbox ticked, it will not be shown as a duplicate. If you have 3 Joe Bloggs (or whatever name you chose to include in Name) with all three being Booked, then all three will be marked as duplicates.

If you copy the formula in Duplicate down the column as far as your Name values go, any new values added to Name will ensure a new row is added and the new Duplicate cell will inherit the formula from above.

Based on your initial description and subsequent explanation, it sounds like the above will do what you want. Unless I'm missing something...

Stacy Meadows

Hi,

I had a similar problem I wanted to highlight duplicate in my postcode column

I overcome this problem by doing the following:

inserting a checkbox column next to postcode column that potentially contained duplicates
using a formula in the checkbox column that ticks if their is a duplicate in the postcode column 1 represents a tick 0 leaves unticked
using conditional formatting that states when box is checked highlight red, with white & bold writing in the postcode column

As you can see it worked...

The formula is important...

=IF(Postcode@row = "", 0, IF(COUNTIFS(Postcode:Postcode, Postcode@row) > 1, 1))

so broken down i used this part to say if postcode column blank leave unchecked "=IF(Postcode@row = "", 0,"

then this part is to count "(COUNTIFS(Postcode:Postcode, Postcode@row)" my individual postcodes to turn it into a numerical value

then the "IF" before the count and the "> 1, 1))" to say if there is more than 1 , to equal a tick mark

....

For yourself you may want blank rows to highlight so it would be equivelent to

=IF(COUNTIFS(Postcode:Postcode, Postcode@row) > 1, 1)

Just to bear in mind for future reference :)

Sign in to join the conversation!

Quick Links

Trending discussions

"Fields marked with an asterisk (*) are required." line on every form.
Hi, "Fields marked with an asterisk (*) are required." is the very first line in the body of every form I create. I don't remember seeing it before (unless you missed filling out a required field; then you would see the message). Is there a way to turn it off or change it? None of my forms are in English so the message is…
Best Way to Create a Stacked Bar Chart from multiple columns
I have a sheet that I pull into a report. The report I then want to use to make a stacked bar chart and put into a dashboard if I can. The columns I am trying to make into a stacked chart are: Lead, project and phase. The issue is that there are several leads and I need the stacked bar chart to show in different colors for…
Dynamic View Issue - Group Sharing
I have a dynamic view setup for a dashboard that has NO restrictions And on the Sharing tab, I have a Group that I created as the additional share access to the view But the individuals in the Group still cannot see the data from the dashboard. Am I missing something? Because when I share the view to them individually…