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

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

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/08/21



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    Mark Cronk ✭✭✭✭✭✭

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

    Be Well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!