Checkbox formula
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

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

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

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

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.

Hello @TJE
I use this solution for flagging duplicate entries  https://community.smartsheet.com/discussion/111316/highlightduplicateentries
Peggy

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 followup on any additional suggestions that are offered once I return.
Thanks so much Smartsheet Community! :)

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

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.


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
Categories
Check out the Formula Handbook template!