Custom Dynamic List

[AmyK023]

I need to create a verification list for each employee that contains their assigned roles and the documents asociated with that role.

Source Data

Table 1: Wherein the supervisor assigns various roles to staff. I have used data validation to restrict the value of each column to either blank or the header.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Name[/TD]
[TD]Role 1[/TD]
[TD]Role 2[/TD]
[TD]Role 3[/TD]
[TD]Role ...[/TD]
[TD]Role 88[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Doe, John[/TD]
[TD]Role 1[/TD]
[TD]Role 2[/TD]
[TD][/TD]
[TD]Role ...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Smith, Jane[/TD]
[TD]Role 1[/TD]
[TD][/TD]
[TD]Role 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Jones, John Paul[/TD]
[TD]Role 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Role 88[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Jones, Sue Ann[/TD]
[TD]Role 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2: The documents associated with each role. This is a pre-populated tab of the workbook each supervisor will receive. There are a variable number of documents for each role. Documents may be repeated in roles. The header names in Table 1 Row 11 and Row 21 are the same. Nothing will change in this table.[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Role 1[/TD]
[TD]Role 2[/TD]
[TD]Role 3[/TD]
[TD]Role ...[/TD]
[TD]Role 88[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Doc Safety[/TD]
[TD]Doc Football[/TD]
[TD]Doc Soccer[/TD]
[TD]Doc Tennis[/TD]
[TD]Doc Weight Room[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Doc Shoes[/TD]
[TD]Doc Tees[/TD]
[TD]Doc Balls[/TD]
[TD]Doc Balls[/TD]
[TD]Doc Nets[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD]Doc Supplies[/TD]
[TD][/TD]
[TD]Doc Racquets[/TD]
[TD]Doc Goal Posts[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD]Doc Concussion[/TD]
[TD][/TD]
[TD]Doc Anger Mgmt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]Doc Clock Mgmt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Doc Anger Mgmt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Doc Balls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 3: Autopopulated. I have created a unique matrix for each employee (each employee will have their own worksheet in the workbook), populated by conditional VLookup (roles) and conditional Hlookup(Docs) for each employee based on their assigned roles.
For John Doe:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]CA[/TD]
[TD]CB[/TD]
[TD]CC[/TD]
[TD]CD[/TD]
[TD]CE[/TD]
[TD]CF[/TD]
[TD]CG[/TD]
[TD]CH[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]Roles[/TD]
[TD]Documents[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Role 1[/TD]
[TD]Doc Safety[/TD]
[TD]Doc Shoes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Role 2[/TD]
[TD]Doc Football[/TD]
[TD]Doc Tees[/TD]
[TD]Doc Supplies[/TD]
[TD]Doc Concussion[/TD]
[TD]Doc Clock Mgmt[/TD]
[TD]Doc Anger Mgmt[/TD]
[TD]Doc Balls[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Role ...[/TD]
[TD]Doc Tennis[/TD]
[TD]Doc Balls[/TD]
[TD]Doc Racquets[/TD]
[TD]Doc Anger Mgmt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Solution Needed

Table 4: Unique for each employee. The role names are populated by a reversal of Excel Magic Trick 608 (see Excel Magic Trick 608: Array Formula To Return Multiple Items - List Students With Free Periods - YouTube). What I'm looking for is a way to create a list of all the associated documents, preferably one (1) entry per unique document, so the employee can verify that he/she has completed all necessary training.

For cell BA44 the formula is: {=IF(ROWS(BA$45:BA45)>$BB$43,"",INDEX($CA$31:$CA$36,SMALL(IF($CA$31:$CA$36<>"",ROW($CA$31:$CA$36)-ROW($CA$31)+1),ROWS(BA$44:BA44))))}

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]BA[/TD]
[TD]BB[/TD]
[TD]BC[/TD]
[TD]BD[/TD]
[TD]BE[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]Employee Name[/TD]
[TD]Doe, John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]Number of Roles Assigned:[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Number of (Unique?) Docs Assigned[/TD]
[TD](???) 13[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]Role Names[/TD]
[TD][/TD]
[TD][/TD]
[TD]Doc Names[/TD]
[TD]Training Complete[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]Role 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]Role 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]Role ...[/TD]
[TD][/TD]
[TD][/TD]
[TD]???[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 916"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AmyK023]

In the absence of any responses, I have gone through and made several changes to the layout of the data collection. I am in the process of creating a macro to populate the list under Doc Names. I'm currently stuck on creating a sort for a list of varying length, but will be able eventually work my way through that as well.

However, if anyone has any formulaic solutions, I would be happy to incorporate as well.