Consolidating data with same headers from multiple worksheets into master worksheet

[moonstex]

Hi Guys,

PLease help me out. I need a VBA code or any other solution to produce the following.

Basically, I have two datasets where the same information is stored for different countries, and I need to merge them into one quite often.


Dataset 1 example


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Sub-type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Canada[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sweden[/TD]
[TD]Vegetable[/TD]
[TD]Tomato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Norway[/TD]
[TD]fruit[/TD]
[TD]Pear[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

For every row where there is no data there is a 0

Dataset 2 example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Sub-type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Azerbaijan[/TD]
[TD]Vegetable[/TD]
[TD]Cucumber[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]India[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Thailand[/TD]
[TD]Vegetable[/TD]
[TD]Tomato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bangladesh[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Exactly same headers and same data but for different set of countries


Consolidated Dataset (this is what I'm trying to automate)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]Type[/TD]
[TD]Sub-Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Canada[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sweden[/TD]
[TD]Vegetable[/TD]
[TD]Tomato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Norway[/TD]
[TD]Fruit[/TD]
[TD]Pear[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Azerbaijan[/TD]
[TD]Vegetable[/TD]
[TD]Cucumber[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]India[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Thailand[/TD]
[TD]VEgetable[/TD]
[TD]Tomato[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Bangladesh[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Basically, I want to copy paste all data from 2 datasets, right after each other without taking into account 0's. Zeros are basically blanks


Please help!

Thanks. S

 

[vanthinh3101]

Thank you, but I do not have permissions to post attachments.

You can give me via email.
My mail is vanthinhnguyenneu310188@gmail.com

 

[mbpressley]

Thank you!

 

[oldbrewer]

Be sure to publish ultimate solution on here as going offline is not allowed - this rule is applied vigorously.

 

[mbpressley]

Hello,

I am new to this. Is it ok to email and then publish what I receive, or not?

Thanks!

 

[oldbrewer]

Mods - please help mbpressley