Formula to check duplicates works...but needs to exclude blanks
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.
Best Answers
-
I think it should look more like this
=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")
-
@Paul H , MUCH appreciated :).
-
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:
- the syntax as-is would check the first two ranges
- How would I modify it to add each additional range? An example would be great!
Answers
-
Nest another IF statement
=IFERROR(IF(ISBLANK(Role@row),"N",IF(CountIf(role30..................................),"")
-
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"), ""))
-
Move the last ,""
=IFERROR(IF(ISBLANK(Role@row), "N", IF(COUNTIFS(Role30:Role39, Role@row) > 1, "Y", "N")),"")
-
@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")))
-
I think it should look more like this
=IF(OR(CONTAINS(Role@row, Role30:Role39), Contains(Role@row, Role54:Role63)),"N", "Y")
-
@Paul H , MUCH appreciated :).
-
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:
- the syntax as-is would check the first two ranges
- How would I modify it to add each additional range? An example would be great!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!