Using two tables to create an output table

[MI_2016]

Hi There

I was wondering the best way to achieve the following. I have the following two tables and want to create a third table which breaks out the total values in Table 1 using the percentage splits listed in Table 2 to calculate the Amount column in the output table.

There could be various descriptions and % splits listed per Item_Ref in Table 2

Hopefully the below examples shows what I am trying to achieve.

Thanks in advance for any assistance!

Table 1
Item_Ref	Total_Value
1001	500
1002	200
1003	250

<tbody>
</tbody>

Table 2
Item_Ref	Description	Percentage
1001	Postage	0.1
1001	Fee	0.03
1001	Item	0.87
1002	Postage	0.1
1002	Fee	0.02
1002	Item	0.88
1003	Postage	0.2
1003	Fee	0.1
1003	Tax	0.2
1003	Item	0.5

<tbody>
</tbody>

Output Table

Item_Ref	Description	Total_Value	Percentage	Amount
1001	Postage	500	0.1	50
1001	Fee	500	0.03	15
1001	Item	500	0.87	435
1002	Postage	200	0.1	20
1002	Fee	200	0.02	4
1002	Item	200	0.88	176
1003	Postage	250	0.2	50
1003	Fee	250	0.1	25
1003	Tax	250	0.2	50
1003	Item	250	0.5	125

<tbody>
</tbody>

 

[mrmmickle1]

If you have three worksheets ("Table1", "Table2", "Output") and your data looks like this:

Excel 2010
	A	B
1	Item_Ref	Total Value
2	1001	500
3	1002	200
4	1003	250
Table1

lor: #161120">Table1</p><br /><br />

Excel 2010
	A	B	C
1	Item_Ref	Description	Percentage
2	1001	Postage	0.1
3	1001	Fee	0.03
4	1001	Item	0.87
5	1002	Postage	0.1
6	1002	Fee	0.02
7	1002	Item	0.88
8	1003	Postage	0.2
9	1003	Fee	0.1
10	1003	Tax	0.2
11	1003	Item	0.5
Table2

Then use this code..... (Assuming "Output" is a blank sheet).

I have commented the code for better understanding

[COLOR=#0000ff]Sub [/COLOR]Test()


[COLOR=#0000ff]Dim[/COLOR] Tbl1LRow[COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]Tbl2LRow[COLOR=#0000ff] As Long[/COLOR]


Tbl1LRow = Sheets("Table1").Cells(Rows.Count, "A").End(xlUp).Row[COLOR=#008000] 'Define Table 1 Last Row[/COLOR]
Tbl2LRow = Sheets("Table2").Cells(Rows.Count, "A").End(xlUp).Row [COLOR=#008000]'Define Table 2 Last Row[/COLOR]


[COLOR=#008000]'Copy and Paste Data and Create Headers[/COLOR]
Sheets("Table2").Range("A1:B" & Tbl2LRow).Copy Sheets("Output").Range("A1")
Sheets("Table2").Range("C1:C" & Tbl2LRow).Copy Sheets("Output").Range("D1")
Sheets("Output").Range("C1") = "Total_Value"
Sheets("Output").Range("E1") = "Amount"


[COLOR=#008000]'Do Calcs[/COLOR]
Sheets("Output").Range("C2:C" & Tbl2LRow) = "=Vlookup(A2, Table1!$A$1:$B$" & Tbl1LRow & ", 2, FALSE)"
Sheets("Output").Range("E2:E" & Tbl2LRow) = "=C2*D2"


[COLOR=#008000]'Copy and Paste Values to eliminate formulas[/COLOR]
Sheets("Output").Range("A1:E" & Tbl2LRow).Copy
Sheets("Output").Range("A1").PasteSpecial xlValues
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

You will get this result:

Excel 2010
	A	B	C	D	E
1	Item_Ref	Description	Total_Value	Percentage	Amount
2	1001	Postage	500	0.1	50
3	1001	Fee	500	0.03	15
4	1001	Item	500	0.87	435
5	1002	Postage	200	0.1	20
6	1002	Fee	200	0.02	4
7	1002	Item	200	0.88	176
8	1003	Postage	250	0.2	50
9	1003	Fee	250	0.1	25
10	1003	Tax	250	0.2	50
11	1003	Item	250	0.5	125
Output