Matching of two conditions with multiple output criteria (SOD-Finance)

Attachment 499218

Dear All,

I am currently working on an internal SOD (Separation of Duties) project. As I am not very versed in Excel I am having problems identifying a way to solve this issue with a smart formula.
Would be wonderful if one of you Excel experts could help.


Issue:
- I have a list with (many individual) users
- Each user has a number of tasks he/she can perform
- These tasks may or may not conflict with each other
- Conflicting tasks are listed in a table (see from picture/Excel expert)

Goal:
- Find a formula, which will identify the conflicting tasks, such that the cell tells me, which tasks is the conflicting one (as listed in the table)

Problem:
- Find appropriate formula that lists the corresponding (conflicting) action on the left
- There are Actions which are conflicting with more than one action:
 Is it possible, to have the formula return i.e. “Action Y” and “Action C” etc.?
 Or is this something I will have to try to solve with Access/not Excel?

I'm having trouble getting post #2 to accept my attachment... maybe now?

Weird... the attachments came back. Here's a new version with a summary column that gives you the list you initially wanted:

The issue at hand has been on hold for a while, so I just checked. The solution seems like a great alternative.
I guess the matrix would work in general as well. I will try it out and report back.
Thank You for the quick response and file example!

My pleasure! The other alternative would be a VBA solution, but it's usually advisable to stick with non-VBA solutions where possible. Assuming the calculation speed is acceptable, you could hide the columns (G:L) that compose the matrix in my last attachment while keeping the resulting "summary" column (M) visible for use.

I'm not sure that I'm understanding your setup clearly, given your description above. My understanding was that the conflicts listed in columns B and C are independent of any particular user. They're spelling out the rules, basically. The reason "ACTION LL" pops up in G4 is that Mike did ACTION A (according to F4), so the formula reports that ACTION LL would be a conflict with ACTION A. If the conflict pairs in Columns B and C are specific to Mike, won't we need an additional column of information to identify whom each pair applies to? Or a list of which actions should be excluded for Mike?

Essentially, regardless of whether a formula or VBA would be the best fit, Excel will need some way of knowing that LL is not available for Mike. Can you clarify how we know that based on the workbook?

Sorry, I guess I explained wrong then:

The set-up is as follows:

"Conflicts" (action 1 and action 2) are the rules, describing which actions are not allowed together (or more precise: are high risk actions). This means that if a user performs action 1, he should not be allowed to perform action 2 (and vise versa).

Basically, this is what I am checking for. Has a user been given actions, such that there is a risk, and if yes, which other action(s) is the risk with?

The formula you set up works great. Except: Column B and C provide ALL possible actions (and these do not all have to be listed for Mike). Therefore, additionally, Excel should check whether "LL" is in column F (=an action that Mike is actually performing/has originally been assigned).
If yes ("LL is in column F/is an action Mike performs"), then it should act according to the formula you gave (i.e. list all the colliding actions from column B/C).
If no ("LL is not in column F/ is not an action Mike performs), it should not list anything at all (because there are no issues for the User/ LL is not being performed by Mike, thus there is no risk pertaining to LL)

Doing anything manually in this case is rather difficult, as I have more than 60 users, each with approx. >4000 (trans-)actions.

Does that clarify?

Ahhh... I think I get it now. So my current model picks up all POTENTIAL conflicts for each action, whereas you want it to look at Mike's other assigned actions and only call out the ACTUAL conflicts based on what Mike has scheduled in column F. Correct?

If my new interpretation is correct, then the simplest fix is to go with the formula below (array entered with Ctrl + Shift + Enter) in G4, filled down and right:

=IFERROR(IF(COUNTIFS($F$4:$F$12,IFERROR(INDEX($C$4:$C$12,SMALL(IF($B$4:$B$12=$F4,ROW($B$4:$B$12)),COLUMN(A:A))-3),INDEX($B$4:$B$12,SMALL(IF($C$4:$C$12=$F4,ROW($C$4:$C$12)),COLUMN(A:A)-COUNTIF($B$4:$B$12,$F4))-3)),$E$4:$E$12,$E4)>0,"ACTION "&IFERROR(INDEX($C$4:$C$12,SMALL(IF($B$4:$B$12=$F4,ROW($B$4:$B$12)),COLUMN(A:A))-3),INDEX($B$4:$B$12,SMALL(IF($C$4:$C$12=$F4,ROW($C$4:$C$12)),COLUMN(A:A)-COUNTIF($B$4:$B$12,$F4))-3)),""),"")

It has largely the same effect as my original effort, but it should leave a blank if the corresponding action is not scheduled for the user, so the matrix will have blanks but be accurate, and the summary column in M should be spot on. For aesthetic purposes, though, you should change the formula in M4 (and fill down) to:

=IFERROR(RIGHT(SUBSTITUTE(G4&H4&I4&J4&K4,"ACTION"," and ACTION"),LEN(SUBSTITUTE(G4&H4&I4&J4&K4,"ACTION"," and ACTION"))-4),"")

or

=IFERROR(RIGHT(SUBSTITUTE(G4&H4&I4&J4&K4,"ACTION"," and ACTION"),LEN(SUBSTITUTE(G4&H4&I4&J4&K4,"ACTION"," and ACTION"))-4),"No Conflicts")

Take a look:

Yes, that's exactly it! The actual conflicts are those of interest. Although obviously there can be more, as you have already picked up in your formula...

Okay, good. Try it on a larger dataset to see if the calculation time is reasonable. It'll help if the ranges in the array formula (covering rows 4:12 in the sample) either perfectly match your actual range or are only slightly larger. The bigger the range, the slower the calculation time. If it proves to be too slow, VBA might be a better alternative.

Thanks for that! I tried it out, yet somehow this time it seems to not give me any output at all. I had to switch around the formula a bit, as my reference (rules in column B and C in the test Excel) are on a different sheet "SOD Conflicts".

I attached an excerpt from the original file (adjusted for sensitive data).

When going to the formula I can identify two possibilities as to why it will not work:
in your formula:
Example_Mike_SOD Conflicts.PNG
- what function does "Column A:A" refer to? I.e. do I need to have it on the tab where I get the output (name/Mike and action), or does it need to be referenced to the column A of the rule set?

- For "COUNTIF($B$4:$B$12,$F4))-3)),$E$4:$E$12,$E4)>0", does E:E have to be on the same sheet as the reference rules?

So sorry for asking so many questions, I am definitely a little overwhelmed with this currently, as I usually perform far more basic Excel work.

Attachment:
Test Mike_Excerpt.xlsx

COLUMN(A:A) can be left alone. We need it to produce a 1 in the first column in which it appears, then a 2 as it's dragged right one column (COLUMN B:B), and so on. Changing it to COLUMN('SOD Conflicts'!A:A) was unnecessary, but completely harmless, since it still produces the desired effect.

I think the issue is that the COUNTIFS part of the formula in your attachment uses ranges of two different sizes, which produces an error, which results in a blank cell. In most places, you refer to your ranges in sheet 'MM' as spanning from row 19 to row 842, but in the middle of the giant formula you throw in a $B$4:$B$842, which doesn't align with the other ranges in the formula, so you get a COUNTIFS with misaligned ranges. The fix should look like this, array entered in Q540:

=IFERROR(IF(COUNTIFS($Q$19:$Q$842,IFERROR(INDEX('SOD Conflicts'!$I$2:$I$100,SMALL(IF('SOD Conflicts'!$H$2:$H$100=$Q540,ROW('SOD Conflicts'!$H$2:$H$100)),COLUMN('SOD Conflicts'!A:A))-1),INDEX('SOD Conflicts'!$H$2:$H$100,SMALL(IF('SOD Conflicts'!$I$2:$I$100=$Q540,ROW('SOD Conflicts'!$I$2:$I$100)),COLUMN('SOD Conflicts'!A:A)-COUNTIF('SOD Conflicts'!$H$2:$H$100,$Q540))-1)),$B$19:$B$842,$B540)>0,"ACTION "&IFERROR(INDEX('SOD Conflicts'!$I$2:$I$100,SMALL(IF('SOD Conflicts'!$H$2:$H$100=$Q540,ROW('SOD Conflicts'!$H$2:$H$100)),COLUMN(A:A))-1),INDEX('SOD Conflicts'!$H$2:$H$100,SMALL(IF('SOD Conflicts'!$I$2:$I$100=$Q540,ROW('SOD Conflicts'!$I$2:$I$100)),COLUMN('SOD Conflicts'!A:A)-COUNTIF('SOD Conflicts'!$H$2:$H$100,$Q540))-1)),""),"")

Alternatively, you could turn all of the $19s into $4s; just make sure they all match. Once that's done, remember that you'll need to fill the formula right one column for every appearance of the most common term in your SOD Conflicts table. Once the formula has been filled right 27 columns to account for the 27 appearances of Master Data on the conflicts sheet, you can use the concatenation/summary formula in column AS:

=IFERROR(RIGHT(SUBSTITUTE(CONCATENATE(R540,S540,T540,U540,V540,W540,X540,Y540,Z540,AA540,AB540,AC540,AD540,AE540,AF540,AG540,AH540,AI540,AJ540,AK540,AL540,AM540,AN540,AO540,AP540,AQ540,AR540),"ACTION"," and ACTION"),LEN(SUBSTITUTE(CONCATENATE(R540,S540,T540,U540,V540,W540,X540,Y540,Z540,AA540,AB540,AC540,AD540,AE540,AF540,AG540,AH540,AI540,AJ540,AK540,AL540,AM540,AN540,AO540,AP540,AQ540,AR540),"ACTION"," and ACTION"))-4),"")

You can then hide columns R:AR. Fill down and you're done! It's worth noting, though, that I don't think there were any actual conflicts in your attached sample. I added two (rows 554 and 575) for test purposes and everything appeared to work. Take a look at the attachment to see if it looks alright:

Yes, it was only a small excerpt. The entire raw data has various issues.
I am seriously amazed at your Excel skills. So quick and every time a solution. Thank You so very much! This will work!

I'm glad I could help; let me know if you have any issues. Good luck!