Two Range lookup and insert to discreet ranges on another worksheet

[cubewarrior]

Excel Gurus,
First off I have found this forum to be amazingly talented and helpful (as well as educational) in the few times I needed help. I am on a PC with Excel 2013 and I am looking for two-fold help:
1) I am looking to insert nested data into specific targetted areas of a blank template. By nested, I mean Insert a range from one table and also insert another range as children to the parent insert by reference to another table.
2) I am looking to understand the code, as I am hoping to learn the hows and whys for my education.
From a taxonomy perspective I think of my data as such:
1) EventMaster Defines Events, Days, and Teams (as well as order of each)
2) TeamMaster Defines Teams, Team Members, and Departments
I am looking to populate an Event card that has a few discrete ranges for data to populate to (By Day), with the Events and Teams from EventMaster (Aligned with Day on template) as well as individual Team Members and Departments matched from Team in EventMaster to Team Members and Department from TeamMaster.
I have a master list of my team members (Sheet "TeamMaster")
Excel 2012

	A	B	C	D	E	F	G	H
1	Team Name	First Name	Last Name	Role	Dept	Commitment	Available	Contact
2	Blue Team	Tom	Brady	Lead	Legal	High	Y	Y
3	Blue Team	Rob	Gronkowski	Crew	Legal	Low	Y	Y
4	Blue Team	Bill	Bellicheck	Crew	Legal	Low	Y	N
5	Green Team	Adam	Sandler	Lead	Sales	High	Y	Y
6	Green Team	Jack	Black	Crew	Sales	Med	Y	N
7	Green Team	Dane	Cook	Crew	Sales	Med	Y	N
8	Yellow Team	Peter	Parker	Lead	Operations	High	Y	Y
9	Yellow Team	Scott	Summers	Crew	Operations	Low	Y	N
10	Pink Team	John	Smith	Lead	Administration	Low	Y	Y
11	Pink Team	Jane	Smith	Crew	Administration	High	Y	N
12	Red Team	Winston	Churchill	Lead	Program Mgmt	Med	Y	Y
13	Red Team	George	Patton	Crew	Program Mgmt	Med	Y	N
14	Red Team	Theodore	Roosevelt	Crew	Program Mgmt	Med	Y	N
15	Red Team	Albert	Einstein	Crew	Program Mgmt	Med	Y	N

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

TeamMaster​

I have a master list of the planned events (Sheet "EventMaster")

Excel 2012

	A	B	C
1	Event	Day	Team
2	Event - Race	1	Green
3	Event - Race	1	Red
4	Event - Race	1	Blue
5	Event - Obstacles	1	Pink
6	Event - Obstacles	1	Yellow
7	Event - Obstacles	1	Green
8	Event - Trivia	2	Yellow
9	Event - Trivia	2	Red
10	Event - Trivia	2	Pink
11	Event - Acronym Match	2	Green
12	Event - Acronym Match	2	Blue
13	Event - Acronym Match	2	Red

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

EventMaster​

I am looking for a Sub that I can assign to a button control that will go to sheet "EventCard" and, for a given day insert a nested return results, being the Event assigned to a given day as well as the team (From "EventMaster) plus insert/expand with the results of the Team (From EventMaster list from sheet "EventMaster" cross referenced with the Team Members from the TeamMaster list on sheet "TeamMaster"). Example of Day 1 desired population below:

Excel 2012

	B	C	D	E	F	G	H	I	J	K	L
2	Day 1						Day 2
3	Event	Team	Team Members		Department		Event	Team	Team Members		Department
4			First Name	Last Name					First Name	Last Name
5	Event - Race	Green	Adam	Sandler	Sales
6	Event - Race	Green	Jack	Black	Sales
7	Event - Race	Green	Dane	Cook	Sales
8	Event - Race	Red	Winston	Churchill	Program Mgmt
9	Event - Race	Red	George	Patton	Program Mgmt
10	Event - Race	Red	Theodore	Roosevelt	Program Mgmt
11	Event - Race	Red	Albert	Einstein	Program Mgmt
12	Event - Race	Blue	Tom	Brady	Legal
13	Event - Race	Blue	Rob	Gronkowski	Legal
14	Event - Race	Blue	Bill	Bellicheck	Legal
15	Event - Obstacles	Pink	John	Smith	Administration
16	Event - Obstacles	Pink	Jane	Smith	Administration
17	Event - Obstacles	Yellow	Peter	Parker	Operations
18	Event - Obstacles	Yellow	Scott	Summers	Operations
19	Event - Obstacles	Green	Adam	Sandler	Sales
20	Event - Obstacles	Green	Jack	Black	Sales
21	Event - Obstacles	Green	Dane	Cook	Sales

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

EventCard​

Any help very appreciated
- CW

 

[MickG]

Try this:-
Results start "A1" of sheet "EventCard"

[COLOR="Navy"]Sub[/COLOR] MG21Dec37
[COLOR="Navy"]Dim[/COLOR] RngT [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngE [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("TeamMaster")
    [COLOR="Navy"]Set[/COLOR] RngT = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngT
    [COLOR="Navy"]If[/COLOR] Not .Exists(Split(Dn.Value, " ")(0)) [COLOR="Navy"]Then[/COLOR]
        .Add Split(Dn.Value, " ")(0), Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Split(Dn.Value, " ")(0)) = Union(.Item(Split(Dn.Value, " ")(0)), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("EventMaster")
    [COLOR="Navy"]Set[/COLOR] RngE = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With


c = 3
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngE.Offset(, 2).Resize(RngE.Count + 1)
[COLOR="Navy"]If[/COLOR] Not Dn.Offset(, -1).Value = Temp Or Dn = "" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Ac = 0 [COLOR="Navy"]Then[/COLOR]
        ReDim ray(1 To RngE.Count + RngT.Count + 3, 1 To 5)
        ray(2, 1) = "Event": ray(2, 2) = "Team": ray(2, 3) = "Team Members": ray(2, 5) = "Department"
        ray(3, 3) = "First Name": ray(3, 4) = "Last Name"
        Ac = Ac + 1
    [COLOR="Navy"]Else[/COLOR]
        Sheets("EventCard").Cells(1, Ac).Resize(c, 5).Value = ray
        [COLOR="Navy"]If[/COLOR] Dn.Value = "" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
        ReDim ray(1 To RngE.Count + RngT.Count + 3, 1 To 5)
        ray(2, 1) = "Event": ray(2, 2) = "Team": ray(2, 3) = "Team Members": ray(2, 5) = "Department"
        ray(3, 3) = "First Name": ray(3, 4) = "Last Name"
        Ac = Ac + 6
        c = 3
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] If


[COLOR="Navy"]For[/COLOR] n = 1 To .Item(Dn.Value).Count
    c = c + 1
    ray(1, 1) = "Day " & Dn.Offset(, -1).Value
    ray(c, 1) = Dn.Offset(, -2).Value: ray(c, 2) = Dn.Value: ray(c, 3) = .Item(Dn.Value)(n).Offset(, 1).Value
    ray(c, 4) = .Item(Dn.Value)(n).Offset(, 2).Value: ray(c, 5) = .Item(Dn.Value)(n).Offset(, 4).Value
[COLOR="Navy"]Next[/COLOR] n
    Temp = Dn.Offset(, -1)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick