Creating dynamic table off of two dynamic lists

nguytravis

New Member
Joined
Nov 16, 2018
Messages
3
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
Employee ListFunding Source
JohnGrant 1
KateGrant 2
AlejandraGrant 3
Grant 4

<tbody>
</tbody>


Output 1: 12 lines are generated; column F shows our 3 employees 4 times while column G shows the 4 funding sources 3 times
EmployeeFunding Source
AlejandraGrant 1
AlejandraGrant 2
AlejandraGrant 3
AlejandraGrant 4
JohnGrant 1
JohnGrant 2
JohnGrant 3
JohnGrant 4
KateGrant 1
KateGrant 2
KateGrant 3
KateGrant 4

<tbody>
</tbody>

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

Well-known Member
Joined
Dec 3, 2018
Messages
9,679
Office Version
2007
Platform
Windows
Try this

Code:
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
 

Forum statistics

Threads
1,085,513
Messages
5,384,104
Members
401,881
Latest member
Dato

Some videos you may like

This Week's Hot Topics

Top