Need VBA Macro to create new sheet2 with the output of Sheeet1

G

gollangi

New Member
Joined
Sep 1, 2017
Messages
1
Hi,

I want to convert the below Sheet 1 excel data to to Sheet 2 Excel Data. Could you please help. Whether in simple code or any macros.


In Sheet 2 Year(Dropdown values(2014,2015,2016), if user selects 2014, then 2014 data only should display. So based on the year the records should display.

And in last row need to get the sum of Units and Values.

Can anyone help to figure out how to implement.

This is Sheet 1.


[TABLE="width: 1"]
<tbody>[TR]
[TD="bgcolor: yellow"]
North America​
[/TD]
[TD="bgcolor: yellow"]
Product​
[/TD]
[TD="bgcolor: yellow"]
Product Code​
[/TD]
[TD="bgcolor: yellow"]
Reporting Countries​
[/TD]
[TD="bgcolor: yellow"]
Trade Flow​
[/TD]
[TD="bgcolor: yellow"]
Import​
[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"]
2014​
[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"]
2015​
[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"]
2016​
[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Unit (T)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Value ($)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Unit (T)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Value ($)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Unit (T)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Value ($)[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 1[/TD]
[TD] 391110[/TD]
[TD] Australia[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 44299[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 2[/TD]
[TD] 391110[/TD]
[TD] Bolivia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 294[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 3[/TD]
[TD] 391110[/TD]
[TD] Canada[/TD]
[TD][/TD]
[TD] 34[/TD]
[TD] 51302[/TD]
[TD] 14[/TD]
[TD] 47779[/TD]
[TD] 6[/TD]
[TD] 19749[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 4[/TD]
[TD] 391110[/TD]
[TD] Chile[/TD]
[TD][/TD]
[TD][/TD]
[TD] 271[/TD]
[TD][/TD]
[TD] 682[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 5[/TD]
[TD] 391110[/TD]
[TD] China[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 7786[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 6[/TD]
[TD] 391110[/TD]
[TD] Colombia[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 1504[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 7[/TD]
[TD] 391110[/TD]
[TD] Guatemala[/TD]
[TD][/TD]
[TD][/TD]
[TD] 339[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 8[/TD]
[TD] 391110[/TD]
[TD] India[/TD]
[TD][/TD]
[TD][/TD]
[TD] 1390[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 40977[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 9[/TD]
[TD] 391110[/TD]
[TD] Indonesia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 1504[/TD]
[TD][/TD]
[TD] 2957[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 10[/TD]
[TD] 391110[/TD]
[TD] Japan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 2572[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 11[/TD]
[TD] 391110[/TD]
[TD] Malaysia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 4452[/TD]
[TD] 4[/TD]
[TD] 17660[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 12[/TD]
[TD] 391110[/TD]
[TD] Mexico[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 330[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 13[/TD]
[TD] 391110[/TD]
[TD] Netherlands[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 1071[/TD]
[TD][/TD]
[TD] 462[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 14[/TD]
[TD] 391110[/TD]
[TD] Norway[/TD]
[TD][/TD]
[TD][/TD]
[TD] 389[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 15[/TD]
[TD] 391110[/TD]
[TD] Portugal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 555[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 16[/TD]
[TD] 391110[/TD]
[TD] Russia[/TD]
[TD][/TD]
[TD][/TD]
[TD] 467[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 17[/TD]
[TD] 391110[/TD]
[TD] South Africa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 971[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 18[/TD]
[TD] 391110[/TD]
[TD] South Korea[/TD]
[TD][/TD]
[TD][/TD]
[TD] 12952[/TD]
[TD][/TD]
[TD] 8056[/TD]
[TD][/TD]
[TD] 3550[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 19[/TD]
[TD] 391110[/TD]
[TD] Sweden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 1366[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 20[/TD]
[TD] 391110[/TD]
[TD] Switzerland[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 385[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 21[/TD]
[TD] 391110[/TD]
[TD] Taiwan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 3[/TD]
[TD] 14365[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 22[/TD]
[TD] 391110[/TD]
[TD] Thailand[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 1838[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 23[/TD]
[TD] 391110[/TD]
[TD] United Kingdom HMRC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 2322[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Test Product 24[/TD]
[TD] 391110[/TD]
[TD] United States[/TD]
[TD][/TD]
[TD] 147[/TD]
[TD] 362560[/TD]
[TD] 120[/TD]
[TD] 313121[/TD]
[TD] 85[/TD]
[TD] 294933[/TD]
[/TR]
</tbody>[/TABLE]



This is Sheet 2.

[TABLE="width: 1"]
<tbody>[TR]
[TD="bgcolor: yellow"]
North America​
[/TD]
[TD="bgcolor: yellow"]
Product​
[/TD]
[TD="bgcolor: yellow"]
HS Code​
[/TD]
[TD="bgcolor: yellow"]
Reporting Countries​
[/TD]
[TD="bgcolor: yellow"]
Trade Flow​
[/TD]
[TD="bgcolor: yellow"]
Year​
[/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Unit (T)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] "] Value ($)[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 1[/TD]
[TD] 468923[/TD]
[TD] Australia[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 44299[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 2[/TD]
[TD] 468924[/TD]
[TD] Bolivia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 3[/TD]
[TD] 468925[/TD]
[TD] Canada[/TD]
[TD][/TD]
[TD] 34[/TD]
[TD] 51302[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 4[/TD]
[TD] 468926[/TD]
[TD] Chile[/TD]
[TD][/TD]
[TD][/TD]
[TD] 271[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 5[/TD]
[TD] 468927[/TD]
[TD] China[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 6[/TD]
[TD] 468928[/TD]
[TD] Colombia[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 1504[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 7[/TD]
[TD] 468929[/TD]
[TD] Guatemala[/TD]
[TD][/TD]
[TD]0[/TD]
[TD] 339[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 8[/TD]
[TD] 468930[/TD]
[TD] India[/TD]
[TD][/TD]
[TD]0[/TD]
[TD] 1390[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 9[/TD]
[TD] 468931[/TD]
[TD] Indonesia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 10[/TD]
[TD] 468932[/TD]
[TD] Japan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 11[/TD]
[TD] 468933[/TD]
[TD] Malaysia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 12[/TD]
[TD] 468934[/TD]
[TD] Mexico[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 13[/TD]
[TD] 468935[/TD]
[TD] Netherlands[/TD]
[TD][/TD]
[TD] 1[/TD]
[TD] 1071[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 14[/TD]
[TD] 468936[/TD]
[TD] Norway[/TD]
[TD][/TD]
[TD][/TD]
[TD] 389[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 15[/TD]
[TD] 468937[/TD]
[TD] Portugal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 16[/TD]
[TD] 468938[/TD]
[TD] Russia[/TD]
[TD][/TD]
[TD][/TD]
[TD] 467[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 17[/TD]
[TD] 468939[/TD]
[TD] South Africa[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 18[/TD]
[TD] 468940[/TD]
[TD] South Korea[/TD]
[TD][/TD]
[TD][/TD]
[TD] 12952[/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 19[/TD]
[TD] 468941[/TD]
[TD] Sweden[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 20[/TD]
[TD] 468942[/TD]
[TD] Switzerland[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 21[/TD]
[TD] 468943[/TD]
[TD] Taiwan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 22[/TD]
[TD] 468944[/TD]
[TD] Thailand[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 23[/TD]
[TD] 468945[/TD]
[TD] United Kingdom HMRC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Canada[/TD]
[TD] Trade Product 24[/TD]
[TD] 468946[/TD]
[TD] United States[/TD]
[TD][/TD]
[TD] 147[/TD]
[TD] 362560[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You must log in or register to reply here.

Similar threads

U
  • Question Question
Create Data Base
Replies
2
Views
278
jolivanes
jolivanes
C
  • Question Question
This is Crazy. How To Copy And Paste 66 Data Using Google Chrome with Macro
Replies
4
Views
824
Corried
C
L
  • Question Question
Macro not working with button and returning error message.
Replies
9
Views
856
RoryA
RoryA
G
  • Question Question
Is there a way to search for a large number of characters?
Replies
1
Views
574
ismii
I
G
  • Question Question
Text to rows formula only able to separate 66 values
Replies
4
Views
704
FlameRetired
F

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top