Creating dynamic table off of two dynamic lists

[nguytravis]

Hi All,
I have two datasets which can vary in length: Employee (column A), and Funding Source (Column B)
I would like to create a table (in columns F and G) which will generate all possible combinations of Column A and Column B.
Ideally, this table would be sorted by column F, and then by Column G.

Scenario 1: 3 employees in column A and 4 funding sources in column B
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee List[/TD]
[TD]Funding Source[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grant 1[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Grant 2[/TD]
[/TR]
[TR]
[TD]Alejandra[/TD]
[TD]Grant 3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grant 4[/TD]
[/TR]
</tbody>[/TABLE]


Output 1: 12 lines are generated; column F shows our 3 employees 4 times while column G shows the 4 funding sources 3 times
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Funding Source[/TD]
[/TR]
[TR]
[TD]Alejandra[/TD]
[TD]Grant 1[/TD]
[/TR]
[TR]
[TD]Alejandra[/TD]
[TD]Grant 2[/TD]
[/TR]
[TR]
[TD]Alejandra[/TD]
[TD]Grant 3[/TD]
[/TR]
[TR]
[TD]Alejandra[/TD]
[TD]Grant 4[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grant 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grant 2[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grant 3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Grant 4[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Grant 1[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Grant 2[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Grant 3[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Grant 4[/TD]
[/TR]
</tbody>[/TABLE]

If column A list (less header) is 4 names long and column B list (less header) is 5 names long, the output list would be 20 rows.

 

[DanteAmor]

Try this

Sub dynamic_table()
    Dim c As Range, r As Range
    Range("F2:G" & Rows.Count).ClearContents
    Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Range("F" & Rows.Count).End(xlUp)(2).Resize(r.Count).Value = c.Value
        Range("G" & Rows.Count).End(xlUp)(2).Resize(r.Count).Value = r.Value
    Next
    Range("F1", Range("G" & Rows.Count).End(xlUp)).Sort _
        key1:=Range("F1"), order1:=xlAscending, key2:=Range("G1"), order2:=xlAscending, Header:=xlYes
End Sub