|
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
Many Workbooks into one spreadsheet
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. |
| All times are GMT +1. The time now is 04:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com