Data Search/Grouping

[leebird]

Hello,

I'm not sure how this can be done, but I have a workbook where what I would like to do is for each employee (B) and their role (D), show what tasks they currently have (F) and compare it to the tasks assigned to their role (sheet 2 - Roles).
Sheet one column D is their role and column F are their tasks. Sheet 2 - column A is the master role and column C are the tasks associated with that role.

Ideally for each employee and their role, is there a way to look up what tasks they currently have and compare it to sheet 2 what tasks they should have (and only have) assigned to their role. Somehow display the results in a separate column(s) on sheet 1? If possible if they have more or less tasks for their role to somehow identify them?


Any help would be appreciated.

[Ochimus]

leebird,

Attacher ALMOST does what you want - but hopefully someone will see the one step that is beating me.

In the "Compared" sheet is a list of User IDs in Col K, and Profiles in Col M

Col A has "Droplists" where you select the name of the User ID.

Cols B finds the matching "Title" for that employee from the Assigned Sheet and enter it automatically:

=IF(A2="","",INDEX(ASSIGNMENT!C:C,MATCH(A2,ASSIGNMENT!A:A,0)))

(If there is no name in A2, B2 stays blank. If there is a name, find the data in Col C of the Assignment sheet matching that name)

Copied across to Col C, same formula finds the matching Profile for the User.

Col D then uses an "Array" to list all the Tasks listed in Col C of the Role sheet for the role in Col B:

=IFERROR(INDEX(ROLES!$A$1:$C$19000,SMALL(IF(ROLES!$A$1:$A$19000=$C$2,ROW(ROLES!$A$1:$A$19000)-1),ROW(ROLES!A1)),3),"")

This has to be copied down manually to the rows below.

Col E then uses a second "Array" to list all the actual tasks in Col F of the Assignment sheet linked to the name in Col A (Again, this is copied down)

=IF(A$2="","",IFERROR(INDEX(ASSIGNMENT!$A$2:$F$19000,SMALL(IF(ASSIGNMENT!$A$2:$A$19000=$A$2,ROW(ASSIGNMENT!$A$2:$A$19000)-1),ROW(ASSIGNMENT!A1)),6),""))

So when you select a name in Col A, it shows you that employee's title and profile, and lists what jobs they should be doing and what they are actually doing.

PROBLEM: For some reason, if there is no "Profile" in the Assignment sheet, Col D on the Compared sheet (Role Tasks) should be blank, but in Row 2 it lists "01_MD_SMARTVIEW", and I can't see why?

Hope this helps

Ochimus

[JohnTopley]

Column D finds a blank (match on C2) in Row 93, subtracts 1 to get row 92 = "01_MD_SMARTVIEW".

Perhaps check C2 is blank: if so, D2 is blank


Try

=IFERROR(INDEX(ROLES!$A$2:$C$19000,SMALL(IF(ROLES!$A$2:$A$19000=$C$2,ROW($A$2:$A$19000)-ROWS($A$2)+1,""),ROWS($A$2)),3),"")

Enter with Ctrl+Shift+Enter


Format cell as 0;0;;@

[Ochimus]

John,

Well spotted! Tried your solution and everything works perfectly.

Ought to wonder why the "Task" array works with "-1", and the "Assignment" didn't? But as leebird has a fully working solution I decided life is too short. . .


Ochimus

[leebird]

works great thank you so much for the final piece

[leebird]

Thank you both very much! This is perfect. I very much appreciate it.