Forum Discussion
Determined access toxic combination.
Hi Everyone -
I want to achieve the expected result as shown in the attached image. On the sample, USER 100 has been assigned with different access highlighted in blue. Some of this access are not allowed with a value of "N", Is there a formula that can achieve this outcome? Basically I need display which toxic [N] combination USER 100 has been assigned to. On line 20 that will be the desired outcome.- As shown USER 100 has been given ACCESS 1 that is toxic against ACCESS 9 that is currently assigned on USER 100.
thank you for your assistance.
- maybe something like this.. but can't get it to work.. can you help me?
=LET(n,INDEX(#REF!,MATCH(B$42,#REF!,0)),IF(FILTER(n:OFFSET(n,ROWS($A$2:$A$11),0),$A$1:$A$11=$A$42)="N",""))NotFunInParties If you are open to Power Query, perhaps the attached file contains a solution for your problem.
It takes both the access matrix and user info as named ranges and performs a couple of merges to find the toxic combinations, as demonstrated in the attached file. Though, I must say that the queries are very basic and, perhaps, a bit rough. With more time, the solution could be made neater.
Riny_van_Eekelen thank you for your suggestion and time put into testing it. I will look into this option, though I am not yet familiar with this feature but this is something worth testing.
