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

Chris Hallo

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.

Current.JPG

Paul H

I think it should look more like this

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

Chris Hallo

@Paul H , MUCH appreciated :).

Chris Hallo

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

Capture.JPG

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!

Paul H

Nest another IF statement

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

Chris Hallo

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"), ""))

Paul H

Move the last ,""

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

Chris Hallo

@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")))

Paul H

I think it should look more like this

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

Chris Hallo

@Paul H , MUCH appreciated :).

Chris Hallo

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

Capture.JPG

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!