duplicates mark 1 with tick box


Hi Experts, I have a list in column with numbers that have different number of duplicates and i want to highlight only one (1st) cell that has duplicates. For example, these 3 numbers:"123", "234", "456"

Let say "123"is repeated 5 times along the column, "234"- 4 times, "345"- 7 times.

Is it possible to marke with a tick box only one cell that contains duplicate value? For example, highlight one cell that contain "123" not all 5 cells, one that contain "234" not all 4 cells, and one that contain "345" not all 7 cells.



  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/03/21

    Hi @Christine Menke

    Hope you are fine, please do the following:

    1- create a helper column call it duplicates ( Text / Number ) type.

    2- use the following formula to define the first duplicates =COUNTIF(Numbers$1:Numbers@row, Numbers@row)

    3- use the following conditional formatting setting ( when "duplicates" = 1 , highlight "Number" )

    the only problem is that you can't convert this formula to a column format formula and you need to copy it to all rows.Maybe one of our experts can develop this formula to become a column format formula.

    the following screenshots show you the result:

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Tibor
    Tibor ✭✭✭


    Thank you for this formula. Anyone knows how I can transfer this formula in column formula w/o any syntax error ?




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tibor I believe you go this solved for in another thread?

    Here is how to get it set as a column formula:

    Insert an auto-number column (called "Auto" in this example) with no special formatting. Then adjust your COUNTIFS to

    =COUNTIFS(Numbers:Numbers, @cell = Numbers@row, Auto:Auto, @cell<= Auto@row)

    If you already have the auto-number column set up and are using special formatting, or you need this to adjust based on actual row number if the sheet is sorted, you would insert a text/number column (called "Row" in this example) and use

    =MATCH(Auto@row, Auto:Auto, 0)

    Now you have the row numbers on the sheet that you can leverage in formulas such as

    =COUNTIFS(Numbers:Numbers, @cell = Numbers@row, Row:Row, @cell<= Row@row)

  • Tibor
    Tibor ✭✭✭

    Yes, thank you