Highlighting duplicate data based on two conditions

11/18/19 Edited 12/09/19

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?

Previous134

Comments

  • Paul NewcomePaul 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)

    thinkspi.com

  • 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 NewcomePaul 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?

    thinkspi.com

  • Hi @Paul Newcome

    Can you kindly assist with a step by step on how to add the formula you supplied and make it work.

    Our clients submit forms from the website. For some reason they submit the form again immediately or after a few days. We want to detect the duplication and delete the entry.

    The form has a column named ID# (a unique 13 digit Identity number for South African citizens).


    Thank you in advance

  • Paul NewcomePaul 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.

    thinkspi.com

  • edited 01/27/20

    Hi @Paul Newcome

    I have this fear for formulas because i never get them right.

    Please see the screenshot and tell me what I'm not doing correctly - I'm getting Unparseable error.

    You might notice that the coma is replaced by a semicolon. The system is changing it. I tried to change it to a coma but it does not change.

    Thank you.

  • Paul NewcomePaul 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?

    thinkspi.com

  • edited 01/27/20

    Hi, the first troubleshooter gave a number 595 (ps...the closing bracket is missing)

    The second tester gives me a the number 1. This happens after first giving Unpearsable errors. I double click and then tab and it gives 1. I removed it and entered it again. Same - error first and then the number 1. Tried again 3rd time. Same results.

  • Paul NewcomePaul 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.

    thinkspi.com

  • I don't want to use the word 'miracle'. But then I entered the formula first in the text/numbner type cell, got the Unpearsable error over and over again. Just when I was giving up, I just changed the cell back to checkbox; saw no error message; entered a duplicate row. And this is the result.

    So if that is how it should work, I have different types of products in the sheet. My favourite product new entries go to the top, Others products go to the bottom.How do I pick up a duplicate if the detected cell is at the bottom - not visible? In other words what I'm saying is the I want latest entry to alert me that there is a duplicate somewhere in the sheet.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • Alright!! @Paul Newcome Works just fine after copying the formula into all rows. Incredible how many duplicates are flagged. Thank you for your patience.


  • Hi @Paul Newcome

    Can you please assist further with how I can expand the formula to flag ID# that is duplicating same Package CashBackSub?

    The CashBackSub is a single selection dropdown with 2 options.


  • Paul NewcomePaul Newcome ✭✭✭✭✭

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


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


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

    thinkspi.com

  • edited 02/07/20

    Hi @Paul Newcome I'm back :-)

    The formula above embarrassed me into silence - tried for hours but does not work.

    Anyway, is there a formula I can use to detect if the person with the same ID# is on any of the 10 sheets? Each sheet must flag if same ID# is found in any of the other sheets?

    Thank you again.

Sign In or Register to comment.