Checkbox formula

TJE

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!

TJE

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!

TJE

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

Marlana K.

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)

TJE

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

Hello @TJE

I use this solution for flagging duplicate entries - https://community.smartsheet.com/discussion/111316/highlight-duplicate-entries

Peggy

TJE

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

@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

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

Hi @TJE ,

Try this:

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

I hope that helps

TJE

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!