Highlighting duplicate data based on two conditions
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?
Comments
-
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,
-
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?
-
This content has been removed.
-
@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.
-
This content has been removed.
-
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?
-
This content has been removed.
-
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.
-
This content has been removed.
-
The formula should have flagged both rows based on the logic. Do you have the formula in all rows?
-
This content has been removed.
-
This content has been removed.
-
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.
-
This content has been removed.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!