duplicates mark 1 with tick box

Christine Menke

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.

Thanks

Bassam Khalil

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:

Tibor

Hello,

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

Ty,

Br,

Tibor

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

Yes, thank you