Generate rows in new table based on tables

[glippy]

Hi. I am trying to generate a table on a separate sheet, based on values in three other tables. Allow me to explain. My workbook now contains three tables:

1. A table with resources (resourceid, resourcename, ...)
2. A table with projects (projectid, projectname, ...)
3. A table with weeks (weekid, year, week)

I am trying to generate a table, combining resourceid, projectid, and weekid, so that each row is a unique combination of these three variables. I have some formulas that I will add to a fourth column, and so on.

So if I would have 3 resources (1-3), 2 projects (1-2), and 4 weeks (1-4), the number of rows that need to be generated is 3 x 2 x 4 = 24. The actual number of resources, projects, and weeks is quite a bit larger of course. But for the same of explaining what I would need, the result would look kind of like this:

resourceid, projectid, weekid, columnx, columny
1, 1, 1, <column with formula>, <etc.>
1, 1, 2, <column with formula>, <etc.>
1, 1, 3, <column with formula>, <etc.>
1, 1, 4, <column with formula>, <etc.>
1, 2, 1, <column with formula>, <etc.>
1, 2, 2, <column with formula>, <etc.>
1, 2, 3, <column with formula>, <etc.>
1, 2, 4, <column with formula>, <etc.>
2, 1, 1, <column with formula>, <etc.>
2, 1, 2, <column with formula>, <etc.>
2, 1, 3, <column with formula>, <etc.>
2, 1, 4, <column with formula>, <etc.>
2, 2, 1, <column with formula>, <etc.>
2, 2, 2, <column with formula>, <etc.>
2, 2, 3, <column with formula>, <etc.>
2, 2, 4, <column with formula>, <etc.>
3, 1, 1, <column with formula>, <etc.>
3, 1, 2, <column with formula>, <etc.>
3, 1, 3, <column with formula>, <etc.>
3, 1, 4, <column with formula>, <etc.>
3, 2, 1, <column with formula>, <etc.>
3, 2, 2, <column with formula>, <etc.>
3, 2, 3, <column with formula>, <etc.>
3, 2, 4, <column with formula>, <etc.>

Is there a way of automatically generating these rows? Especially as resources and projects are added to the sheet, it would help a lot so that I don't need to manually add them.

Thanks for your help!

 

[Akuini]

Hi, @glippy. Welcome to the Forum.
Try this:
I assumed the three tables name are Table1,Table2 & Table3

Sub a1174196a()
'https://www.mrexcel.com/board/threads/generate-rows-in-new-table-based-on-tables.1174196/
Dim tbl1 As ListObject
Dim tbl2 As ListObject
Dim tbl3 As ListObject
Dim nTbl As ListObject
Dim n As Long, i As Long
Dim a, b, c

Application.ScreenUpdating = False
With ActiveSheet
    Set tbl1 = .ListObjects("Table1")
    Set tbl2 = .ListObjects("Table2")
    Set tbl3 = .ListObjects("Table3")
End With



n = tbl1.DataBodyRange.Rows.Count * tbl2.DataBodyRange.Rows.Count * tbl3.DataBodyRange.Rows.Count
    
    'change "Sheet2" and "A2" to suit
    Sheets("Sheet2").ListObjects.Add(xlSrcRange, Sheets("Sheet2").Range("A2").Resize(n + 1, 5), , xlYes).Name = "newTable"
    Set nTbl = Sheets("Sheet2").ListObjects("newTable")
    With nTbl
        .HeaderRowRange(1) = "resourceid"
        .HeaderRowRange(2) = "projectid"
        .HeaderRowRange(3) = "weekid"
    End With

For Each a In tbl1.DataBodyRange.Columns(1).Value
    For Each b In tbl2.DataBodyRange.Columns(1).Value
        For Each c In tbl3.DataBodyRange.Columns(1).Value
            i = i + 1
            With nTbl.DataBodyRange
                .Cells(i, 1) = a
                .Cells(i, 2) = b
                .Cells(i, 3) = c
            End With
        Next
    Next
Next

Application.ScreenUpdating = True
End Sub

Example:

glippy - generate-rows-in-new-table-based-on-tables.1174196.xlsm
	A	B	C	D
1
2		resourceid	Column2
3		Zachary
4		Rodney
5		Stephen
6
7
8		projectid	Column2
9		Q
10		H
11
12
13		weekid	Column2
14		W01
15		W02
16		W03
17		W04
18
Sheet1

Result:

glippy - generate-rows-in-new-table-based-on-tables.1174196.xlsm
	A	B	C	D	E	F
1
2	resourceid	projectid	weekid	Column4	Column5
3	Zachary	Q	W01
4	Zachary	Q	W02
5	Zachary	Q	W03
6	Zachary	Q	W04
7	Zachary	H	W01
8	Zachary	H	W02
9	Zachary	H	W03
10	Zachary	H	W04
11	Rodney	Q	W01
12	Rodney	Q	W02
13	Rodney	Q	W03
14	Rodney	Q	W04
15	Rodney	H	W01
16	Rodney	H	W02
17	Rodney	H	W03
18	Rodney	H	W04
19	Stephen	Q	W01
20	Stephen	Q	W02
21	Stephen	Q	W03
22	Stephen	Q	W04
23	Stephen	H	W01
24	Stephen	H	W02
25	Stephen	H	W03
26	Stephen	H	W04
27
Sheet2