I have a sheet that tracks hundreds of cell phones, so it's really important to ensure existing records are updated rather than added as new rows. I can flag columns like Asset Tag and Phone Number with this formula =IF(COUNTIF([Asset Tag]:[Asset Tag],[Asset Tag]@row)>1,1,0) .
The problem is that this doesn't work for the IMEI column. The reason is that these numbers start with 0 (zero). SmartSheet appends a ' to the data (e.g. 0123 is '0123). If you take the '0 out, the formula works. But then the data is wrong. Is there a way to make this work?
Below is the formula and structure I'm using. The formula examines row 1 down to the current row and if it finds a duplicate it returns a 1 which checks the Checkbox. I also tried it with full alpha (e.g. ABCD) and got the expected return. The FAA column is Text/Number and the Duplicate is a Checkbox. Let me know how I can help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
I'm not able to duplicate your error. The formula =IF(COUNTIF([IMEI]:[IMEI],[IMEI]@row)>1,1,0) works for me. COUNTIF works with text and numbers in the same range. Smartsheets treats your leading 0 lines as text. It should match them to other text strings. Are you getting an error or just the wrong answer?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
It works in the same sheet, using the same formula, with cells that are numeric and alpha characters. It only breaks on cells that start with a zero. For example '015099000210745. I suspect it may have something to do with the leading apostrophe '.
Does it work for you with data like that (e.g. '0123 )?
Yes it works for me. IThe ' makes smartsheet use the string as text instead of a number. That's what allows the leading 0 to remain visible. I mixed text strings (leading 0s) and numbers in the same column and my count functions worked and returned the right answers.
If the string is text, Smartsheet should match that text string to other text strings an return the count. Is the Column set as a text/number format? I'm not sure what else to look at with your sheet. Maybe someone else will have an idea. Lots of smart and creative people in the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
I created a new sheet to test this out. As you can see, ABC and 123 flag dups no problem. When I have a numeric field that starts with a 0, SmartSheet prepends the '. The same formula that works for the other rows does not flag the "0123" entries.
If this is working for you, can you share your formula?
Below is the formula and structure I'm using. The formula examines row 1 down to the current row and if it finds a duplicate it returns a 1 which checks the Checkbox. I also tried it with full alpha (e.g. ABCD) and got the expected return. The FAA column is Text/Number and the Duplicate is a Checkbox. Let me know how I can help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
I was using "=IF(COUNTIF([IMEI]:[IMEI],[IMEI]@row)>1,1,0)". This worked for everything except the fields where the leading character was a zero. Plugged in "=IF(CountIF(IMEI$1:[email protected], @cell =[email protected])>1,1,0)" and problem solved!
Mark Cronk ✭✭✭✭✭✭
