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 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)
-
Yes, thank you
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives