# Using two tables to create an output table

#### MI_2016

##### New Member
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>

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### mrmmickle1

##### Well-known Member
If you have three worksheets ("Table1", "Table2", "Output") and your data looks like this:

Excel 2010
AB
1Item_RefTotal Value
21001500
31002200
41003250
Table1

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

Excel 2010
ABC
1Item_RefDescriptionPercentage
21001Postage0.1
31001Fee0.03
41001Item0.87
51002Postage0.1
61002Fee0.02
71002Item0.88
81003Postage0.2
91003Fee0.1
101003Tax0.2
111003Item0.5
Table2

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

I have commented the code for better understanding
Code:
``````[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
ABCDE
1Item_RefDescriptionTotal_ValuePercentageAmount
21001Postage5000.150
31001Fee5000.0315
41001Item5000.87435
51002Postage2000.120
61002Fee2000.024
71002Item2000.88176
81003Postage2500.250
91003Fee2500.125
101003Tax2500.250
111003Item2500.5125
Output

Replies
0
Views
134
Replies
4
Views
430
Replies
1
Views
362
Replies
2
Views
679
Replies
7
Views
216

1,190,782
Messages
5,982,883
Members
439,803
Latest member
sushilneupane

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back