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

	A	B	C	D
1	Country	Type	Sub-type	Amount
2	USA	Fruit	Apple	3
3	Canada	Fruit	Orange	5
4	Sweden	Vegetable	Tomato	1
5	Norway	fruit	Pear	4
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0
9	0	0	0	0
10	0	0	0	0
11	0	0	0	0

<tbody>
</tbody>

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

Dataset 2 example

	A	B	C	D
1	Country	Type	Sub-type	Amount
2	Azerbaijan	Vegetable	Cucumber	4
3	India	Fruit	Orange	1
4	Thailand	Vegetable	Tomato	1
5	Bangladesh	Fruit	Apple	2
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0

<tbody>
</tbody>

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


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

	A	B	C	D
1	Country	Type	Sub-Type	Amount
2	USA	Fruit	Apple	3
3	Canada	Fruit	Orange	5
4	Sweden	Vegetable	Tomato	1
5	Norway	Fruit	Pear	4
6	Azerbaijan	Vegetable	Cucumber	4
7	India	Fruit	Orange	1
8	Thailand	VEgetable	Tomato	1
9	Bangladesh	Fruit	Apple	2

<tbody>
</tbody>

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

 

[oldbrewer]

highlight rows 2 to 5 of data set 2 , copy, paste into A6 of dataset 1 ?

 

[moonstex]

This is just an example. In reality, both datasets have about 2000 rows and 150 columns and are updated daily. At the same times there are satellite tools that produce analysis based on the data in both. Before they were all in the same files, but due to multiple people working on the data, we decided to split based on countries. Now every time we need to run analysis from satelite tools, we have to copy paste. Would be amazing to make it more efficient somehow. Macro, or some kind of combines Pivot Tables, automatically refreshable sheet i don't know...

 

[moonstex]

Does anyone have a possible solution? Please let me know, would really appreciate it

 

[oldbrewer]

Country

Type

Sub-type

Amount

Country

Type

Sub-type

Amount

USA

Fruit

Apple

3

Azerbaijan

Vegetable

Cucumber

4

Canada

Fruit

Orange

5

India

Fruit

Orange

1

Sweden

Vegetable

Tomato

1

Thailand

Vegetable

Tomato

1

Norway

fruit

Pear

4

Bangladesh

Fruit

Apple

2

0

0

0

0

0

0

0

0

this macro takes the valid rows from the 2 data sets

and merges them

rrow = 19

For j = 1 To 100

If Cells(j, 1) = 0 Then GoTo 100

rrow = rrow + 1

Cells(rrow, 1) = Cells(j, 1)

Cells(rrow, 2) = Cells(j, 2)

Country

Type

Sub-type

Amount

Cells(rrow, 3) = Cells(j, 3)

USA

Fruit

Apple

3

Cells(rrow, 4) = Cells(j, 4)

Canada

Fruit

Orange

5

Next j

Sweden

Vegetable

Tomato

1

100 For j = 2 To 100

Norway

fruit

Pear

4

If Cells(j, 9) = 0 Then GoTo 200

Azerbaijan

Vegetable

Cucumber

4

rrow = rrow + 1

India

Fruit

Orange

1

Cells(rrow, 1) = Cells(j, 9)

Thailand

Vegetable

Tomato

1

Cells(rrow, 2) = Cells(j, 10)

Bangladesh

Fruit

Apple

2

Cells(rrow, 3) = Cells(j, 11)

Cells(rrow, 4) = Cells(j, 12)

Next j

200 End Sub

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[vanthinh3101]

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

	A	B	C	D
1	Country	Type	Sub-type	Amount
2	USA	Fruit	Apple	3
3	Canada	Fruit	Orange	5
4	Sweden	Vegetable	Tomato	1
5	Norway	fruit	Pear	4
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0
9	0	0	0	0
10	0	0	0	0
11	0	0	0	0

<tbody>
</tbody>

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

Dataset 2 example

	A	B	C	D
1	Country	Type	Sub-type	Amount
2	Azerbaijan	Vegetable	Cucumber	4
3	India	Fruit	Orange	1
4	Thailand	Vegetable	Tomato	1
5	Bangladesh	Fruit	Apple	2
6	0	0	0	0
7	0	0	0	0
8	0	0	0	0

<tbody>
</tbody>

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


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

	A	B	C	D
1	Country	Type	Sub-Type	Amount
2	USA	Fruit	Apple	3
3	Canada	Fruit	Orange	5
4	Sweden	Vegetable	Tomato	1
5	Norway	Fruit	Pear	4
6	Azerbaijan	Vegetable	Cucumber	4
7	India	Fruit	Orange	1
8	Thailand	VEgetable	Tomato	1
9	Bangladesh	Fruit	Apple	2

<tbody>
</tbody>

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

Hi,
For example, Dataset 1 is in Sheet1, Dataset 2 is in Sheet2.
I make a Sheet3 with Form to Consolidate Data

	A	B	C	D
1	Country	Type	Sub-type	Amount

<tbody>
</tbody>

VBA code is:
Sub Consolidate_Data()
Dim Ws As Worksheet, I As Long, sR As Long, Rng As Range

For Each Ws In ThisWorkbook.Sheets
If Ws.Name <> "Sheet3" Then
With Ws
I = 2
Do While .Cells(I, 1) > 0
Set Rng = .Cells(I, 1).Resize(, 4)
With Sheet3
sR = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & sR).Resize(, 4) = Rng.Value
End With
I = I + 1
Loop
End With
End If
Next Ws
End Sub

 

[mbpressley]

Hello,

I also would like to consolidate data. I have 7 companies (agencies) with 3 worksheets each (Worksheet A,B,C). Different agency related info on each worksheet. I would like to consolidate the data from all 7 companies and create one worksheet A, one worksheet B, and one Worksheet C. I have tried using multiple tables to create a pivot table and consolidating worksheets. The results have been less than stellar, but I have no experience with VBA codes. Can you help please?

 

[vanthinh3101]

Hello,

I also would like to consolidate data. I have 7 companies (agencies) with 3 worksheets each (Worksheet A,B,C). Different agency related info on each worksheet. I would like to consolidate the data from all 7 companies and create one worksheet A, one worksheet B, and one Worksheet C. I have tried using multiple tables to create a pivot table and consolidating worksheets. The results have been less than stellar, but I have no experience with VBA codes. Can you help please?

I can solve your problem.
But I need your sample file to write VBA codes.

 

[mbpressley]

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

 

[oldbrewer]

use google sheets - I think that is allowed here - mods please clarify.