If a postal code doesn't match another line item for the same employee ID - check a box

Heather Mize

 I need to compare the postal code in two or more rows with the same Employee ID to make sure they match each other. If they don't then to check a box for Attention: Zip Code.

For instance, below, we have 4 line items for one person. The City is Atlanta and the State is GA and Postal code is 30339. Since there is also a line item that is Irvine CA 92606 I would need the box checked Attention: Zip Code for that because it needs to be reviewed.

Nick Burrus

There's no "smart" zipcode database for Smartsheet yet. But, you can do this:

1. Ensure that you have a checkbox column
2. Go to Automation
3. Choose Change Cell Value workflow
4. Press Create
5. Change when Zipcode changes to Any Value
6. Add Condition to Filter
7. Where Zipcode to select IS NOT ONE OF
8. Check ALL Atlanta zipcodes
9. in change cell value select your checkbox column
10. Choose Checked
11. press save
12. Then create conditonal formatting rules etc from here

Here are ALL The Atlanta zipcodes:

30002

30021

30030

30032

30033

30067

30072

30079

30080

30084

30303

30305

30306

30307

30308

30309

30310

30311

30312

30313

30314

30315

30316

30317

30318

30319

30324

30326

30327

30328

30329

30337

30338

30339

30340

30341

30342

30344

30345

30346

30354

30360

30363

Heather Mize

The comparison I am needing is just to look at the zip codes that are showing for that employee ID and if one is different, then to check the box. In the screenshot, since the zip is for Georgia 30339 and there is also one included as California 92606 - I would need the check box checked. We need all of the zip codes showing for one person to be the same. If something is different, then I need to get it corrected. Does that make sense? I am having the hardest time with this one. :)

Thank you!

Heather Mize

@NBurrus - I appreciate the instructions on how to setup the automation in that format! I am quite sure that will come in handy as we are setting up so many of our processes in Smartsheets. Trying to learn all I can! :)

Nick Burrus

@Heather Mize Unfortunately that's a bit different to find the multiple different entries. The best way to do this would be a helper column and do a reference for the correct match then verify that zipcode about it. Do you have a clean database sheet where everyone has the correctzipcode to begin with?

Heather Mize

Okay trying a different route - maybe this will be better. I only need to indicate if there is more than one zipcode showing for one employee id. If there is, then indicate a checkbox for that. I am also going to have to do the same for city/state and Designated Pay. So I've created a list of what the information should be - so if it's different, then to highlight or check a box in a helper column. So if I have the sheet reference the below, would that work?

the following is my actual report I'm working off of. Would the reference sheet work in order to compare all the things I need for it?

I really appreciate your help!

Heather Mize

So to further clarify, if the pay rate on the reference sheet does not match what is showing as the pay rate on the report, can it check a box?