perfected_tipoff920
New Member
- Joined
- Aug 24, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- MacOS
I am trying to identify based on if a specific USERNAME what ACCESS_TYPE they have based on the evaluation of all the values of RESPONSIBILITY_TYPE
what formula can I use in Col G to obtain these results?
Thanks
If same username has all responsibility_types = 'READ_ONLY' then ACCESS_TYPE = 'READ_ONLY'
If same username has all responsibility_types = 'READ/WRITE' then ACCESS_TYPE = 'READ/WRITE'
If same username has all responsibility_type contains both 'READ-ONLY' and 'READ/WRITE' then ACCESS_TYPE = 'MIXED'
Expected Results for all rows of JDOE in ACCESS_TYPE would be 'READ_ONLY', for JBLOGGS would be 'READ/WRITE' for FNERK would be 'MIXED'
BEFORE
EXPECTED RESULTS
what formula can I use in Col G to obtain these results?
Thanks
If same username has all responsibility_types = 'READ_ONLY' then ACCESS_TYPE = 'READ_ONLY'
If same username has all responsibility_types = 'READ/WRITE' then ACCESS_TYPE = 'READ/WRITE'
If same username has all responsibility_type contains both 'READ-ONLY' and 'READ/WRITE' then ACCESS_TYPE = 'MIXED'
Expected Results for all rows of JDOE in ACCESS_TYPE would be 'READ_ONLY', for JBLOGGS would be 'READ/WRITE' for FNERK would be 'MIXED'
BEFORE
A | B | C | D | E | F | G | |
1 | DEPT | EMP_NO | USERNAME | NAME | RESPONSIBILITY | RESPONSIBILITY_TYPE | ACCESS_TYPE |
2 | IT | 555 | JDOE | JOHN DOE | FINANCE_INQUIRY | READ-ONLY | |
3 | IT | 555 | JDOE | JOHN DOE | BILLING_INQUIRY | READ-ONLY | |
4 | IT | 555 | JDOE | JOHN DOE | SALES_INQUIRY | READ-ONLY | |
5 | BILLING | 111 | JBLOGGS | JOE BLOGGS | BILLING_CLERK | READ/WRITE | |
6 | BILLING | 111 | JBLOGGS | JOE BLOGGS | INVOICE_MGR | READ/WRITE | |
7 | BILLING | 333 | FNERK | FRED NERK | BILLING_CLERK | READ/WRITE | |
8 | BILLING | 333 | FNERK | FRED NERK | FINANCE_INQUIRY | READ-ONLY | |
EXPECTED RESULTS
A | B | C | D | E | F | G | |
1 | DEPT | EMP_NO | USERNAME | NAME | RESPONSIBILITY | RESPONSIBILITY_TYPE | ACCESS_TYPE |
2 | IT | 555 | JDOE | JOHN DOE | FINANCE_INQUIRY | READ-ONLY | READ_ONLY |
3 | IT | 555 | JDOE | JOHN DOE | BILLING_INQUIRY | READ-ONLY | READ_ONLY |
4 | IT | 555 | JDOE | JOHN DOE | SALES_INQUIRY | READ-ONLY | READ_ONLY |
5 | BILLING | 111 | JBLOGGS | JOE BLOGGS | BILLING_CLERK | READ/WRITE | READ/WRITE |
6 | BILLING | 111 | JBLOGGS | JOE BLOGGS | INVOICE_MGR | READ/WRITE | READ/WRITE |
7 | BILLING | 333 | FNERK | FRED NERK | BILLING_CLERK | READ/WRITE | MIXED |
8 | BILLING | 333 | FNERK | FRED NERK | FINANCE_INQUIRY | READ-ONLY | MIXED |