Is it possible to flag duplicates when the data starts with a 0 (zero)?

Eric Busse

Hi!

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?

Thanks a ton!

Eric

Mark Cronk

Hi @Eric Busse ,

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

Mark Cronk

Hi @Eric Busse ,

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

Mark Cronk

Eric Busse

Hi Mark,

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 )?

Thanks a ton!

Eric

Mark Cronk

Hi Eric,

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

Eric Busse

Hi Mark,

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?

Thanks,

Eric

Mark Cronk

Hi @Eric Busse ,

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

Eric Busse

That did it! Thank you so much Mark.

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:IMEI@row, @cell = IMEI@row)>1,1,0)" and problem solved!

All the Best,

Eric

Mark Cronk

Glad you were able to figure it out. Thanks for contributing to the Community.

Be Well,

Mark

jeanniesry_4

Hi @Mark Cronk -

I created a helper column with this formula =COUNTIFS([Supplier Lot #:]:[Supplier Lot #:], [Supplier Lot #:]@row) so I can set a conditional format to highlight duplicate values in this lot supplier lot column. However, duplicate values are not highlighted if the values have zeros in front of them.

So I am using the formula you suggested to Eric for the values with leading zeros to be counted as duplicate, but it's giving me #UNPARSEABLE. Can you help see what I did wrong here?