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.
Thanks
Answers

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:
bassam.khalil2009@gmail.com
☑️ 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"

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

@Tibor I believe you go this solved for in another thread?
Here is how to get it set as a column formula:
Insert an autonumber 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 autonumber 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)

Yes, thank you