Highlighting duplicate data based on two conditions

Karoline Isaksen

Hi,

I want to make a formula to highlight whenever to cells contain the same data on the same date. I'm working with a marketing calendar and want to find a way to highlight whenever two campaigns are planned to go out to the same country on the same date. So, if two (or more) cells contain the same date as well as the same country locales. 

The only formula I can find is to check if there are duplicates in ONE column, but I want to check all columns against each other. 

I have created a helper column in which to place the formula and thinking to make conditional formatting, once it's ready. The conditional formatting should apply if more than one row has identical data in cell 1 (date) and cell 2 (country).

Is this possible?

Paul Newcome

Try something like this in a checkbox type column...

 

=IF(COUNTIFS([Date Column]:[Date Column], [Date Column]@row, [Country Column]:[Country Column], [Country Column]@row) > 1, 1)

Karoline Isaksen

Hi Paul,

Thanks so much for your suggestion. The formula works in the sense that it highlights if the data is identical. I need, however, the formula to highlight if there is duplicate data, meaning that if some data is the same. Example:

1,2,3,4,5,6

7,6,9,10,1

The formula should work with the above data, because the number 1 is in both cells. Can you help with that?

Thanks,

Paul Newcome

For this you would need to parse the data out so that each entry is in its own cell then do a comparison that way. How many entries could you possibly have in a single cell?

Paul Newcome

@director62476 All you would need to do is add a flag or checkbox column. Then you can use a formula such as

=IF(COUNTIFS([ID #]:[ID #], [ID #]@row) > 1, 1)

This will count how many times the ID # on that particular row is found in the column. If it is found more than once, it will flag all rows that are duplicated.

Paul Newcome

The semicolon vs comma is regional. If it is inputting the semicolon, then I assume that is what you need to use.

The syntax is correct, and your column name matches. I don't see any extra spaces or parenthesis. The formula should be working.

Let's work on some trouble shooting...

Change the DupCheck column to a text/number type, and just enter

=COUNT([ID#]:[ID#)

What is the result?

If it produces a number as opposed to an error, lets move on to

=COUNTIFS([ID#]:[ID#], [ID#]@row)

What is the result of that?

Paul Newcome

That's odd. Try the IF statement again, but then try the same steps you just listed of leaving the cell, re-entering it, then tabbing out of it.

At this point I would suggest reaching out to support, as that is definitely not supposed to act that way.

Paul Newcome

The formula should have flagged both rows based on the logic. Do you have the formula in all rows?

Paul Newcome

We would only have to expand on our COUNTIFS to include the additional range/criteria.

=IF(COUNTIFS([ID #]:[ID #], [ID #]@row, CashBackSub:CashBackSub, CashBackSub@row) > 1, 1)

This will only flag rows where both the ID# AND the CashBackSub columns are duplicated within the same row.

Paul Newcome

@director62476 What do you mean by "does not work"? Is it generating an error or unexpected results?

I am not sure I understand your question though...

You have 11 sheets. The main sheet plus 10 others. You want a formula on each of the 10 sheets that will flag if the ID is found in the main sheet?

Paul Newcome

Ah. Ok. My apologies. I forgot that you needed semicolons. Try changing it and see if that corrects the issue.

Never be shy about asking for help. All that does is make it even longer before you can get some help. No judgement here.

New Request: I am still not sure I follow. You want a formula on all 10 sheets to tell you if an ID is on any of the other 9?

Paul Newcome

Ok. So let's just go ahead and start on sheet 1...

Would you like it to tell you which sheet(s) the other name is so that you can narrow down your search?

For example: The ID# is found on Sheets 3 and 8. Would you want it to just check a box or flag a cell, or would you rather see "Sheet 3 and Sheet 8"?

Paul Newcome

You won't necessarily have to remove it. You could either add to it so that the same flag appears for dups on the same sheet or the others, you could add another flag column to differentiate between same sheet vs others, or you could even add in a column for each sheet to flag. The main difference is going to be how many columns you want to use.

Here's the basics...

=CONTAINS([ID#], {Sheet 2 ID# Column})

Since CONTAINS is a true/false, it will flag if true or leave blank if false. Using this logic, you can create a nested IF to have one column flagged if it is found in one or more other sheets, drop the formula as-is into it's own column (one column for each sheet) to flag which sheet(s) you are looking for, or add the nested IF to your current dup formula to have same sheet and other sheets all flagged in the same column.

Nested IF example:

=IF(CONTAINS([ID#], {Sheet 2 ID# Column}) = true, 1, IF(CONTAINS([ID#], {Sheet 3 ID# Column}) = true, 1, IF(CONTAINS([ID#], {Sheet 4 ID# Column}) = true, 1, ..........................................................

Paul Newcome

You need an @row reference after the [ID#] column reference.

=IF(CONTAINS([ID#]@row; {DRINV ID# }) = true; 1)

Paul Newcome

How did you create your cross sheet reference? I notice a space between "ID#" and the closing curly bracket. Does that belong there?

Paul Newcome

Make sure you are following the proper steps to create the cross sheet reference.