Checkbox formula

Options

Hello,

I've created a checkbox formula to check the box if there is a duplicate value in the IMEI # column. In theory, the formula is working fine for the data already there, but when I enter new "duplicate" row as a test, the formula does not work. The checkbox appears without being checked. Can someone please provide some insight as to why this might be happening? Please see the formula below. Thanks!



Best Answer

  • TJE
    TJE ✭✭✭
    Answer ✓
    Options

    Hello Okey,


    Thanks for the suggestion! I was actually able to get it to work with this formula yesterday:

    =IF(COUNTIFS([IMEI #]:[IMEI #], [IMEI #]@row <> " ", [IMEI #]:[IMEI #], [IMEI #]@row) > 1, 1, 0)

    However, if I encounter any issues with it, I will be sure to give the formula you suggested a try!


    Thanks!

Answers

  • TJE
    TJE ✭✭✭
    Options

    In addition, fyi, I converted the formula to a column formula.

  • Marlana K.
    Marlana K. ✭✭✭✭✭✭
    Options

    Hi TJE,

    We use a similar process for duplicate submissions and I had the same issue. Change your formula to use > 0, 1 and see if it corrects the issue.


    This is what I used : IF(COUNTIFS(SID:SID, SID@row, Date:Date, <Date@row) > 0, 1)

    MARLANA KALINOWSKI

    Sr. Business Analysts / Smartsheet Solutions

    National Pharmacy Services | Genoa Healthcare

  • TJE
    TJE ✭✭✭
    Options

    Hi Marlana,

    Thanks for your assistance! Unfortunately, no it did not work as intended. The modification actually caused additional boxes which are not duplicates to also be checked.

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options
  • TJE
    TJE ✭✭✭
    Options

    Hi Peggy,

    Thanks for your assistance! Yes, the formula worked, however, it returns a "1" when the for the blank cells in that column as well. After modifying the formula you provided as well as the one I provided, I discovered the issue with the highlighted section of the formula (see screenshot). When I do not use it, the formula works but returns a 1 for the blank cells as well. So, in essence, they both work to a degree, but neither provide exactly what is needed.


    I will be on vacation starting now and returning next week. I will follow-up on any additional suggestions that are offered once I return.


    Thanks so much Smartsheet Community! :)

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @TJE

    Try this:

    =IF([IMEI #]@row <> "", IF(COUNTIFS([IMEI #]:[IMEI #], @cell = [IMEI #]@row, helper:helper, @cell <= helper@row) >= 2, 1))

    I created a helper column. This column is an # auto number column with nothing specified.

    This does not check the box for the original entry, just the duplicate entries. I added conditional formatting to highlight the entire row if Checkbox is checked.

    Hope you have a good week away. Let me know.

    Thanks, Peggy

  • TJE
    TJE ✭✭✭
    Options

    Hello Peggy!

    Thanks so much for your assistance. Unfortunately, no, it did not work as intended. It checked some IMEI #s that were not duplicates as well.

    It seems the formulas are checking the box even if there are only few numbers in the IMEI number that are the same.


    Thanks.

  • Okey
    Okey ✭✭✭✭
    edited 11/30/23
    Options

    Hi @TJE ,

    Try this:

    =IF(ISNUMBER(IMEI@row), COUNTIFS(IMEI:IMEI, IMEI@row) > 1, 1).

    I hope that helps

  • TJE
    TJE ✭✭✭
    Answer ✓
    Options

    Hello Okey,


    Thanks for the suggestion! I was actually able to get it to work with this formula yesterday:

    =IF(COUNTIFS([IMEI #]:[IMEI #], [IMEI #]@row <> " ", [IMEI #]:[IMEI #], [IMEI #]@row) > 1, 1, 0)

    However, if I encounter any issues with it, I will be sure to give the formula you suggested a try!


    Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!