# Formula to check duplicates works...but needs to exclude blanks

Options
✭✭✭✭

Good morning! I'm hoping another perspective will point out the obvious here for me :).

The formula is working correctly in Test_A2 if the Role is the same within the given range. Where I'm having a problem is how/where to add in isblank so that Test_A2 equals N if the Role is blank within the range. Ideally, I'd like to keep this in one column without the need for an additional helper but am not opposed if its the best way.

• ✭✭✭✭✭✭
Options

I think it should look more like this

=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")

• ✭✭✭✭
Options

@Paul H , MUCH appreciated :).

• ✭✭✭✭
Options

Greetings, @Paul H and Community! I could use some help with an IF formula - please :).

Background: There are years (0 through 5) that have processes (1 through 25) and each process has roles (1 through 10). In the attached image, I'm adding a formula in the Test_A1 that should check within all roles/processes per year to determine if it is unique (used only once) within the entire year.

I do already have an Excel file with the ranges so want to ensure:

1. the syntax as-is would check the first two ranges
2. How would I modify it to add each additional range? An example would be great!

• ✭✭✭✭✭✭
Options

Nest another IF statement

=IFERROR(IF(ISBLANK(Role@row),"N",IF(CountIf(role30..................................),"")

• ✭✭✭✭
Options

Thanks, @Paul H. I entered the below and am getting an incorrect argument set error. Is the syntax off?

=IFERROR(IF(ISBLANK(Role@row), "N", IF(COUNTIFS(Role30:Role39, Role@row) > 1, "Y", "N"), ""))

• ✭✭✭✭✭✭
Options

Move the last ,""

=IFERROR(IF(ISBLANK(Role@row), "N", IF(COUNTIFS(Role30:Role39, Role@row) > 1, "Y", "N")),"")

• ✭✭✭✭
Options

@Paul H, that worked great (in Test_A0), thank you! For Test_A01, I would like to add a formula that checks the range for process 1 as well as process 2 (there are 25 processes total so I'm thinking a long OR with the ranges for each). For that, I tried the below and get an error. Any insight there?

=if(OR(Role30:Role39,Contains(Role@row)OR(Role54:Role63,Contains(Role@row),"N","Y")))

• ✭✭✭✭✭✭
Options

I think it should look more like this

=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")

• ✭✭✭✭
Options

@Paul H , MUCH appreciated :).

• ✭✭✭✭
Options

Greetings, @Paul H and Community! I could use some help with an IF formula - please :).

Background: There are years (0 through 5) that have processes (1 through 25) and each process has roles (1 through 10). In the attached image, I'm adding a formula in the Test_A1 that should check within all roles/processes per year to determine if it is unique (used only once) within the entire year.

I do already have an Excel file with the ranges so want to ensure:

1. the syntax as-is would check the first two ranges
2. How would I modify it to add each additional range? An example would be great!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!