Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only common
in some of the 12 and there are few column names not common but just in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

Hi Ezrael,

See Ron de Bruin's sample code at:

http://www.rondebruin.nl/copy3.htm


---
Regards,
Norman


"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only common
in some of the 12 and there are few column names not common but just in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only common
in some of the 12 and there are few column names not common but just in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

Thanks, but this is kind of what I have in my data from 3 of the 12
workbooks:

workbook 1, sheet 1:

PL COU IND TYP SUR R CA CB
101 Usa Car Int Lab 1 2 2
102 Usa Elec Nat Lab 1 3 3
201 canada mach Int Lab 1 4 5 workbook 2, sheet 2:
PL COU IND TYP SUR R CA CC
101 Usa Car Int Man 1 3 5
101 Usa Car Int Man 2 4 5
102 Usa Elec Nat Man 1 3 4
102 Usa Elec Nat Man 2 4 2
201 canada mach Int Man 1 3 4
201 canada mach Int Man 2 5
workbook 3, sheet 3
PL COU IND TYP SUR R CA CB CD
101 usa car int Sup 1 3 4
102 usa elec nat Sup 1 2 4 4
201 canada mach int Sup 1 3 2


And I would like to have, not just these 3 but all others, merge in a
workbook as:

PL COU IND TYP SUR R CA CB CC CD
101 usa car int Lab 1 2 2
101 usa car int Man 1 3 5
101 usa car int Man 2 4 5
....
201canada mach int Sup 1 3 2

Thanks in advance,
PY & Associates wrote:
We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only common
in some of the 12 and there are few column names not common but just in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

We do not understand why you restrict your self with 3 (?) workbooks. Step 2
can be repeated until all your workbooks have been exhausted please.

Cheers

"ezrael" wrote in message
ups.com...
Thanks, but this is kind of what I have in my data from 3 of the 12
workbooks:

workbook 1, sheet 1:

PL COU IND TYP SUR R CA CB
101 Usa Car Int Lab 1 2 2
102 Usa Elec Nat Lab 1 3 3
201 canada mach Int Lab 1 4 5 workbook 2, sheet 2:
PL COU IND TYP SUR R CA CC
101 Usa Car Int Man 1 3 5
101 Usa Car Int Man 2 4 5
102 Usa Elec Nat Man 1 3 4
102 Usa Elec Nat Man 2 4 2
201 canada mach Int Man 1 3 4
201 canada mach Int Man 2 5
workbook 3, sheet 3
PL COU IND TYP SUR R CA CB CD
101 usa car int Sup 1 3 4
102 usa elec nat Sup 1 2 4 4
201 canada mach int Sup 1 3 2


And I would like to have, not just these 3 but all others, merge in a
workbook as:

PL COU IND TYP SUR R CA CB CC CD
101 usa car int Lab 1 2 2
101 usa car int Man 1 3 5
101 usa car int Man 2 4 5
...
201canada mach int Sup 1 3 2

Thanks in advance,
PY & Associates wrote:
We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only
common
in some of the 12 and there are few column names not common but just
in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

As you may see in the data sample, there are no header rows. The first
column contains the data of the plant code and it will repeat many
times since many of the data comes from different subjects from the
same source (same plant from same country). On top of this I don´t
know much of VBA

Thanks
PY & Associates wrote:
We do not understand why you restrict your self with 3 (?) workbooks. Step 2
can be repeated until all your workbooks have been exhausted please.

Cheers

"ezrael" wrote in message
ups.com...
Thanks, but this is kind of what I have in my data from 3 of the 12
workbooks:

workbook 1, sheet 1:

PL COU IND TYP SUR R CA CB
101 Usa Car Int Lab 1 2 2
102 Usa Elec Nat Lab 1 3 3
201 canada mach Int Lab 1 4 5 workbook 2, sheet 2:
PL COU IND TYP SUR R CA CC
101 Usa Car Int Man 1 3 5
101 Usa Car Int Man 2 4 5
102 Usa Elec Nat Man 1 3 4
102 Usa Elec Nat Man 2 4 2
201 canada mach Int Man 1 3 4
201 canada mach Int Man 2 5
workbook 3, sheet 3
PL COU IND TYP SUR R CA CB CD
101 usa car int Sup 1 3 4
102 usa elec nat Sup 1 2 4 4
201 canada mach int Sup 1 3 2


And I would like to have, not just these 3 but all others, merge in a
workbook as:

PL COU IND TYP SUR R CA CB CC CD
101 usa car int Lab 1 2 2
101 usa car int Man 1 3 5
101 usa car int Man 2 4 5
...
201canada mach int Sup 1 3 2

Thanks in advance,
PY & Associates wrote:
We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only
common
in some of the 12 and there are few column names not common but just
in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.

You mentioned that there are no header rows, but your sample clearly showed:
workbook 1, sheet 1:

PL COU IND TYP SUR R CA CB

workbook 2, sheet 2:
PL COU IND TYP SUR R CA CC

workbook 3, sheet 3
PL COU IND TYP SUR R CA CB CD

Also you indicated that you are not familiar with VBA.

We suggest you pass our notes to your service provider and we believe he
would be able to do it for you at minimal cost.

Cheers

"ezrael" wrote in message
ups.com...
As you may see in the data sample, there are no header rows. The first
column contains the data of the plant code and it will repeat many
times since many of the data comes from different subjects from the
same source (same plant from same country). On top of this I don´t
know much of VBA

Thanks
PY & Associates wrote:
We do not understand why you restrict your self with 3 (?) workbooks. Step
2
can be repeated until all your workbooks have been exhausted please.

Cheers

"ezrael" wrote in message
ups.com...
Thanks, but this is kind of what I have in my data from 3 of the 12
workbooks:

workbook 1, sheet 1:

PL COU IND TYP SUR R CA CB
101 Usa Car Int Lab 1 2 2
102 Usa Elec Nat Lab 1 3 3
201 canada mach Int Lab 1 4 5 workbook 2, sheet 2:
PL COU IND TYP SUR R CA CC
101 Usa Car Int Man 1 3 5
101 Usa Car Int Man 2 4 5
102 Usa Elec Nat Man 1 3 4
102 Usa Elec Nat Man 2 4 2
201 canada mach Int Man 1 3 4
201 canada mach Int Man 2 5
workbook 3, sheet 3
PL COU IND TYP SUR R CA CB CD
101 usa car int Sup 1 3 4
102 usa elec nat Sup 1 2 4 4
201 canada mach int Sup 1 3 2


And I would like to have, not just these 3 but all others, merge in a
workbook as:

PL COU IND TYP SUR R CA CB CC CD
101 usa car int Lab 1 2 2
101 usa car int Man 1 3 5
101 usa car int Man 2 4 5
...
201canada mach int Sup 1 3 2

Thanks in advance,
PY & Associates wrote:
We would do it in two steps:

1
Labels
copy header rows from each spreadsheet into column A of final
worksheet
(fws)
sort,
delete duplicates 'this ensures no misspell label is missed out
put in row1 as label

This can be automated but does not worth the effort.

2
Now read next available row number in fws
read each column label in worksheet in each workbook
use HLookUp to match column in fws
tug data of this column accordingly
read next column label
increment row number
do for next workbook

Cheers

"ezrael" wrote in message
ups.com...
Is it possible to do the following?

I have 12 separate spreadsheets in 12 workbooks that contain data
in.
Some column names are common to all 12 (PLANT COUNTRY INDUSTRY TYPE
SURVEY RESPONDENT), other column names (different data) are only
common
in some of the 12 and there are few column names not common but just
in
one sheet.

Want to be able to merge the data from the 12 workbooks into one.


Appreciate your help on this.