Check one list against another

[itrisk]

Hello,

Please help...

I need to test a list of items against a set of rules as follows:

List
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]User[/TD]
[TD]System Roles[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Role 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Role 2[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Role 3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Role 4[/TD]
[/TR]
</tbody>[/TABLE]










Rules
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Rule[/TD]
[TD]System Roles[/TD]
[TD]System Roles[/TD]
[/TR]
[TR]
[TD]Rule 1[/TD]
[TD]Role 1[/TD]
[TD]Role 2[/TD]
[/TR]
[TR]
[TD]Rule 2[/TD]
[TD]Role 3[/TD]
[TD]Role 4[/TD]
[/TR]
[TR]
[TD]Rule 3[/TD]
[TD]Role 1[/TD]
[TD]Role 3[/TD]
[/TR]
[TR]
[TD]Rule 4[/TD]
[TD]Role 2[/TD]
[TD]Role 3[/TD]
[/TR]
</tbody>[/TABLE]


What I need to check:
If the user "John" has those 4 roles, which rules does he break?

So, in this case "John" breaks all for 4 rules as his 4 roles break the different combinations.

So the output could look like:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User[/TD]
[TD]Rules broken[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


Background: This would be used to see if a user's access would be in violation of a rule set that is made up of combinations of system roles a single user shouldn't have...


Any help would be appreciated!

Many thanks

 

[PhilS2520]

Hello itrisk

How many rules must a user violate to be denied access? I think what you can do is have a counter, and if this counter reaches a certain value then access will be denied.

So, if I violate rule 1 I’ll have a 1 in my counter, if I violate rules 1 and 2 then I will have 3 in my counter. The permutations and/or combinations of a set of 4 elements is not that many, so this can be done easily.

Hope this helps.

 

[wideboydixon]

It would be easier if the final report table could be organised by user like this:

Book1
	A	B
1	User	System Role
2	John	Role 1
3	John	Role 2
4	John	Role 3
5	John	Role 4
Roles

Book1
	A	B	C
1	Rule	System Role	System Role
2	Rule 1	Role 1	Role 2
3	Rule 2	Role 3	Role 4
4	Rule 3	Role 1	Role 3
5	Rule 4	Role 2	Role 3
Rules

Book1
	A	B	C	D	E
1	User	Rule 1	Rule 2	Rule 3	Rule 4
2	John	Broken	Broken	Broken	Broken
Check

Cell Formulas
Range	Formula
B2	=IF(COUNTIFS(Roles!$A:$A,$A2,Roles!$B:$B,INDEX(Rules!$B:$B,MATCH(B$1,Rules!$A:$A,0)))+COUNTIFS(Roles!$A:$A,$A2,Roles!$B:$B,INDEX(Rules!$C:$C,MATCH(B$1,Rules!$A:$A,0)))>1,"Broken","")

Copy B2 formula across and down. Is that an acceptable compromise?

WBD

 

[itrisk]

Hi WBD,

That is an acceptable compromise!

But would this work for a list of multiple users?

Thanks

 

[itrisk]

Hi WBD,

That is an acceptable compromise!

But would this work for a list of multiple users?

Thanks

Just tested on real data and it works a charm

Thanks a million!

 

[wideboydixon]

:D Cool! Glad I could help.

WBD