Abstract

Given a table of roles and which rights are associated with them, and a table of forbidden right combinations - i.e. defining which rights are not to be granted together, how can you now derive the table of forbidden role combinations - i.e. a table which shows which roles cannot be granted together?

Example

Role 1 owns right 1, role 2 contains rights 2 and 4, and role 4 contains rights 4 and 5.

If we do not allow right 1 to be granted together with right 4, then the interesting conclusion is that role 1 can never be given together with role 2 nor role 4:

Roles_and_Rights

The formulas:

The grey area in the second table mirrors the values of the white cells in the same table.

The array formula in the output table in cells B19:E22:

=REPT("x",SIGN(MMULT(--(TRANSPOSE(B3:E7)="x"),MMULT(--(B11:F15="x"),--(B3:E7="x")))))

Download

Please read my Disclaimer.

Roles_and_Rights.xlsx [11 KB Excel file, open and use at your own risk]