How to fill a

[elmacay]

Hello everyone,

Edit: Using Excel 2016

I am having difficulties describing my question, so I didn't know how to search for it on the forum. Hope I can make myself clear through a picture. I guess I'm not the first one to ask this.

So, I want to create a filled table 3. This table consists of an x number of IDs, with for every ID a y number of Attributes (properties). This creates a table of in this case 3x3 unique combinations. The purpose of this table is to have it filled out with values, and after to be uploaded. The steps would be 1) fill table with IDs; 2) fill table with Attributes; 3) press button with macro --> tadaa, filled table number 3; 4) fill out values; 5) save and upload.

I'm not sure if working with tables here helps or hinders. I thought it would make it easier, because I could refer to the tables in the code.

Any help is greatly appreciated!

Cheers, Elmacay

 

[KRice]

You may want to update your profile to show the version of Excel that you use. You want to create a table consisting of all "combinations" of items in two columns. This could be done either with formulas or VBA. Please respond back with your version and preference for constructing the table.

 

[elmacay]

My apologies I forgot to change the name of the thread after writing it. And there doesn't seem to be a way to do that.
The title should be 'How to automatically fill a table combining two other tables'.

 

[elmacay]

Thank you, KRice, good tips.
I think a macro would be most handy, since the product of IDs and Attributes can run up quickly.

 

[KRice]

Here is one way to do it with formulas: You can hide columns H and I if you'd like...they create the combinations of indexes for pulling info from each of the other tables. As you add information to the source tables, just pull the formulas in the combination table down plenty of extra rows to accommodate the larger number of combinations.

mrexcel_20200416.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	IDs		Attributes			Counts		RowNum	Row_Att	ID	Attribute	Value
3	ID1		Attrib1		ID	3		1	1	ID1	Attrib1
4	ID2		Attrib2		Attributes	4		1	2	ID1	Attrib2
5	ID3		Attrib3		num rows	12		1	3	ID1	Attrib3
6			Attrib4					1	4	ID1	Attrib4
7								2	1	ID2	Attrib1
8								2	2	ID2	Attrib2
9								2	3	ID2	Attrib3
10								2	4	ID2	Attrib4
11								3	1	ID3	Attrib1
12								3	2	ID3	Attrib2
13								3	3	ID3	Attrib3
14								3	4	ID3	Attrib4
15
16
17
18
19
20
21
22
23
24
25
26
27
Sheet21

Cell Formulas
Range	Formula
H3:H22	H3	=IF(ROWS(H$3:H3)<=cnt_ID*cnt_Att,QUOTIENT(ROWS(H$3:H3)-1,cnt_Att)+1,"")
I3:I22	I3	=IF(H3<>"",MOD(ROWS(H$3:H3)-1,cnt_Att)+1,"")
J3:J22	J3	=IFERROR(OFFSET(Table1[[#Headers],[IDs]],H3,0),"")
K3:K22	K3	=IFERROR(OFFSET(Table2[[#Headers],[Attributes]],I3,0),"")
F3	F3	=COUNTA(Table1[IDs])
F4	F4	=COUNTA(Table2[Attributes])
F5	F5	=cnt_ID*cnt_Att

Named Ranges
Name	Refers To	Cells
cnt_Att	=Sheet21!$F$4	F5, H3:I22
cnt_ID	=Sheet21!$F$3	F5, H3:H22
tbl_Att	=Table2[[#All],[Attributes]]	K3:K22
tbl_ID	=Table1[[#All],[IDs]]	J3:J22

 

[KRice]

And a solution with VBA could be used to build the combination list from two source columns as shown here. Source lists in columns A & B. Combinations written into columns D & E. Adjust the VBA code references to suit.

mrexcel_20200416.xlsm
	A	B	C	D	E
1
2	ID1	Attrib1		ID1	Attrib1
3	ID2	Attrib2		ID1	Attrib2
4	ID3	Attrib3		ID1	Attrib3
5	ID4	Attrib4		ID1	Attrib4
6		Attrib5		ID1	Attrib5
7				ID2	Attrib1
8				ID2	Attrib2
9				ID2	Attrib3
10				ID2	Attrib4
11				ID2	Attrib5
12				ID3	Attrib1
13				ID3	Attrib2
14				ID3	Attrib3
15				ID3	Attrib4
16				ID3	Attrib5
17				ID4	Attrib1
18				ID4	Attrib2
19				ID4	Attrib3
20				ID4	Attrib4
21				ID4	Attrib5
Sheet37

Option Explicit

Sub combination()

Dim wb1 As Workbook
Dim ws1 As Worksheet

' Set references
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Sheet37")

' Get last non-empty rows
Dim lrw1, lrw2 As Integer
lrw1 = ws1.Cells(1000, "A").End(xlUp).Row
lrw2 = ws1.Cells(1000, "B").End(xlUp).Row

' Create loops to cycle through two lists
Dim i, j, k As Integer
k = 2                         ' row where data begins
For i = 2 To lrw1             ' loop starts on row where data begins
For j = 2 To lrw2             ' loop starts on row where data begins
    ws1.Cells(k, "D").Value = ws1.Cells(i, "A").Value
    ws1.Cells(k, "E").Value = ws1.Cells(j, "B").Value
    k = k + 1
Next j
Next i

End Sub

 

[elmacay]

Thanks a lot, KRice. Works like a charm. I'll tinker with the code to help me understand what it does.

 

[KRice]

You're welcome. I'm happy to help.