Flagging duplicate registration plates

vvvriska

Hi, this is my first post on the forum. I use smartsheets for work but I have created this account to use the forums. 

I am attempting to create a formula that will allow me to use conditional formatting when two entries use the same registration plate number. The sheet is set out like this (im unsure if the link at the bottom of the post is publicly viewable)

 

The columns are:

Nickname| Rego | Duplicate Flag

I would like for the Duplicate flag column to output a value for whether or not a registration is used twice in the rego column, like 0 or 1. 

I have tried to use =IF(COUNTIF(Rego:Rego, Rego1) > 1, 0) but it is not working, I am unsure why. 

 

https://app.smartsheet.com/b/home?lx=pJtmE07wlyAKJdWBrUuduA

 

p_bureau

Hi,

 

we can't access your sheet without your permission !

 

However your formula should work. You also can only use the "COUNTIF" part, and use a conditionnal formatting when the value is >1. This is what I personnally use in my work.

 

Maybe you could attach a screenshot of your data?

 

Best Regards,

 

Paul.

Paul Newcome

=IF(ISBLANK(Rego@row), 0, IF(COUNTIF(Rego:Rego, =Rego@row) > 1, 1, 0))

 

Drag fill this on down through your flag column. It will search entire Rego column for the value in that particular row and count how many times it finds it. If it finds more than one, it will "Flag" it. The ISBLANK portion is to avoid flagging any rows that are empty.

vvvriska

Paul you're a life saver! Thank you so much! 

 

vvvriska

Hi, the comment below yours worked for me, but thank you for trying to help out! 

vvvriska

in our system, we use - instead of leaving cells blank to avoid making it look like we made a mistake and left something out. How do i work around this?

 

thanks again!!

 

edit: ive got a work around but its probably clumsy, i put =IF(FIND("-", Rego@row) > 0, "YES", "NO") in the next column, and then applied additional formatting to overwrite the first rule (that you stated) if there's a "-", but it's not a very elegant solution, I was wondering if you had a better one?

Paul Newcome

=IF(Rego@row = "-", 0, IF(COUNTIF(Rego:Rego, =Rego@row) > 1, 1, 0))

 

All you really need to do is change the first IF statement from looking for a blank cell to looking for the -.

 

That'll keep you from needing that extra row and isn't as easy to break. The more moving parts you have, the easier it is to break. Just a little something to keep in mind.

 

Also... If you look through the templates provided by Smartsheet, you will find one titled "Smartsheet Formula Examples". It is a GREAT reference that you can use to manipulate and play with testing what happens if you change parts of already working formulas, and since it is a template, if you happen to get it all jacked up and accidentally save, you can just reuse the template and delete the old sheet.

vvvriska

thankyou! i really appreciate all the support. I'm not sure why, but I seem to break formulas as soon as I try to edit them. I think I need to sit down one day and just learn how to use them from the beginning all over again, lol. 

I'll definitely try out that template, too! 

 

 

edit: I just added a little bit to the formula since i should have mentioned we have line breaks in between entries for visual flow, so we technically do have blank cells, but by looking at your formula i was able to make a change to it that didnt immediately break it, so im learning! thank you.  

for anyone reading this thread in the future, the new and final formula is: 

=IF(Rego@row = "-", 0, IF(ISBLANK(Rego@row), 0, IF(COUNTIF(Rego:Rego, =Rego@row) > 1, 1, 0)))

 

Paul Newcome

Formulas here in Smartsheet can certainly be a bit different. I am certainly liking your new formula. One thing I can say is that Smartsheet does provide a lot of reference material on formulas. A basic understanding of how they work and some trial and error will definitely go a long way. One recommendation I have and still use today is this:

 

Break it down. If you want it to preform multiple actions, then type it out, each part in it's own cell. Once you get the individual parts working, it's just a matter of putting them in the right order and adding some syntax, maybe a couple of parenthesis get moved, or you throw in an extra comma or a plus or what have you.

 

Best of luck to you and don't hesitate to ask away if you have any other questions. The only stupid question is the one left unasked.   

vvvriska

That's a great sentiment! Slowly but surely, I seem to be getting the hang of them. I've got all the basics down, I just need some practice with where brackets go, spaces, etc. Thank you!