Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
Hello,
I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
When you use Data Consolidation in Excel, it only uses the top row and left
column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
Hi Ron,
thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
The way your data is structured does not lend itself to being easily
consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
Yes if it's possible to retrieve the consolidate table from access to excel
again... why not. The problem is dat I've never open a doc in access, but if you say I don't need a lot of experience in access I'm ready to do so... Can you guide me? "Ron Coderre" wrote: The way your data is structured does not lend itself to being easily consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
OK....Here you go
Note: There's a bit of prep work to set things up, but you only have to do this once. Using your example data First, create a range for each set of data Here's how: Select the data range InsertNameDefine Names in Workbook: (enter a unique name here like: rngDataSht1) Refers to: (your already selected range) Click the [OK] button repeat for each sheet of data, changing the name each time: rngDataSht2, rngDataSht3, etc Save the file In MS Access (MSA), select the Tables tab Click the [New] button and use design mode Enter these fields (as text fields): Liner AB AC AD BC BD Save the table structure Name:tblConsolData (Do not set a primary key) Now, select the Macros tab Click the [New] button In the Action column select TransferSpreadsheet from the dropdown list. At the bottom of the window fill out the table as follows: Transfer type: Import Spreadsheet type: (use the default for Excel) Table Name: tblConsolData File Name: (Enter the complete path to the file, including the file name) Has Field Names: Yes Range: rngDataSht1 (or whatever name you used) Next, select the black triangle at the top of the window to select that row Edit|Copy Select the next row down Edit|Paste (to set commands to pull in the next data range) Switch to the bottom of the window and set the next range name to be pulled (Repeat for as many ranges as you need.) Save and close the macro sheet as: ConsolXLData To run the consolidation Double-click the ConsolXLData macro sheet To view the consolidated data, double click on the tblConsolData table (If you don't see your data...STOP and we'll figure out what needs to be adjusted) Save and close the Access database (I'll assume it's called MyData.mdb) Using Excel: Select a blank sheet Data|Import External Data|Import Data Browse to the MSA mdb file using the Look In dropdown at the top of the window. When you find the file, double-click it to see the list of tables Double-click the tblConsolData table Select where in the Excel file you want to imported data to start Click the [OK] button....That should bring in your consolidated data Any other time you want to refresh the data: Right-click on one of the column headings in the Excel data range Select Refresh Data Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Yes if it's possible to retrieve the consolidate table from access to excel again... why not. The problem is dat I've never open a doc in access, but if you say I don't need a lot of experience in access I'm ready to do so... Can you guide me? "Ron Coderre" wrote: The way your data is structured does not lend itself to being easily consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
Hi Ron,
thks a lot for your clear help !! However, I have a problem when running the macro. it says "External table is not in the expected format". So i was wondering what i did wrong ... (to be honest i don't know) but i was wondering if the macro is retriving the data from all my workbook or do i need to put allmy sheet in one excelworkbook? Also i was no sure about the file name: to you meant something like that: "C:\MSLM test\Mars sheets for consolidation 1" the "Mars Sheets for consolidation1" beeing the folder where all the excel workbooks are. thks a lot brgds, florence "Ron Coderre" wrote: OK....Here you go Note: There's a bit of prep work to set things up, but you only have to do this once. Using your example data First, create a range for each set of data Here's how: Select the data range InsertNameDefine Names in Workbook: (enter a unique name here like: rngDataSht1) Refers to: (your already selected range) Click the [OK] button repeat for each sheet of data, changing the name each time: rngDataSht2, rngDataSht3, etc Save the file In MS Access (MSA), select the Tables tab Click the [New] button and use design mode Enter these fields (as text fields): Liner AB AC AD BC BD Save the table structure Name:tblConsolData (Do not set a primary key) Now, select the Macros tab Click the [New] button In the Action column select TransferSpreadsheet from the dropdown list. At the bottom of the window fill out the table as follows: Transfer type: Import Spreadsheet type: (use the default for Excel) Table Name: tblConsolData File Name: (Enter the complete path to the file, including the file name) Has Field Names: Yes Range: rngDataSht1 (or whatever name you used) Next, select the black triangle at the top of the window to select that row Edit|Copy Select the next row down Edit|Paste (to set commands to pull in the next data range) Switch to the bottom of the window and set the next range name to be pulled (Repeat for as many ranges as you need.) Save and close the macro sheet as: ConsolXLData To run the consolidation Double-click the ConsolXLData macro sheet To view the consolidated data, double click on the tblConsolData table (If you don't see your data...STOP and we'll figure out what needs to be adjusted) Save and close the Access database (I'll assume it's called MyData.mdb) Using Excel: Select a blank sheet Data|Import External Data|Import Data Browse to the MSA mdb file using the Look In dropdown at the top of the window. When you find the file, double-click it to see the list of tables Double-click the tblConsolData table Select where in the Excel file you want to imported data to start Click the [OK] button....That should bring in your consolidated data Any other time you want to refresh the data: Right-click on one of the column headings in the Excel data range Select Refresh Data Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Yes if it's possible to retrieve the consolidate table from access to excel again... why not. The problem is dat I've never open a doc in access, but if you say I don't need a lot of experience in access I'm ready to do so... Can you guide me? "Ron Coderre" wrote: The way your data is structured does not lend itself to being easily consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
Ok thks for your help i'll try to verify all this and then i'll try again.
I'll keep you informed "Ron Coderre" wrote: OK...let's see what I can do to help. External table is not in the expected format<< Since access works with tables, your data must be in a table format. That means avoid blank column headings, spaces in column headings (MyCol....not: My Col), etc. Also, since it appeared that data columns might contain either numbers or text, I had you define each column as text. One way to help find the problem is to manually try to import the data into the MSA table: Start by opening your consolidation table Edit|Select All Records Press the [Delete] key to clear any data that might already be in the table File|Get External Data|Import Files of type: Excel files Select your file Click the [Import] button Select: Show Named Ranges Select a range name to import....Click [Next] Check: First rows contains Column Headings....Click [Next] Check: In an existing table...select your consolidation table....Click [Next] Click the [Finish] button If you run into any problems you can't figure out, note the error and post it. Repeat for each range i was not sure about the file name<< On the macro sheet, you enter the complete path of the file, including the name: example: "C:\myFolder\MyDataFile.xls" Since the sheets are in different workbooks, there will be a different file path and name for each line of the macro. Post your progress. Once you get comfortable with this method you'll see that it's extremely powerful. I've used it to consolidate literally hundreds of Excel tables in hardly any time at all. *********** Best Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks a lot for your clear help !! However, I have a problem when running the macro. it says "External table is not in the expected format". So i was wondering what i did wrong ... (to be honest i don't know) but i was wondering if the macro is retriving the data from all my workbook or do i need to put allmy sheet in one excelworkbook? Also i was no sure about the file name: to you meant something like that: "C:\MSLM test\Mars sheets for consolidation 1" the "Mars Sheets for consolidation1" beeing the folder where all the excel workbooks are. thks a lot brgds, florence "Ron Coderre" wrote: OK....Here you go Note: There's a bit of prep work to set things up, but you only have to do this once. Using your example data First, create a range for each set of data Here's how: Select the data range InsertNameDefine Names in Workbook: (enter a unique name here like: rngDataSht1) Refers to: (your already selected range) Click the [OK] button repeat for each sheet of data, changing the name each time: rngDataSht2, rngDataSht3, etc Save the file In MS Access (MSA), select the Tables tab Click the [New] button and use design mode Enter these fields (as text fields): Liner AB AC AD BC BD Save the table structure Name:tblConsolData (Do not set a primary key) Now, select the Macros tab Click the [New] button In the Action column select TransferSpreadsheet from the dropdown list. At the bottom of the window fill out the table as follows: Transfer type: Import Spreadsheet type: (use the default for Excel) Table Name: tblConsolData File Name: (Enter the complete path to the file, including the file name) Has Field Names: Yes Range: rngDataSht1 (or whatever name you used) Next, select the black triangle at the top of the window to select that row Edit|Copy Select the next row down Edit|Paste (to set commands to pull in the next data range) Switch to the bottom of the window and set the next range name to be pulled (Repeat for as many ranges as you need.) Save and close the macro sheet as: ConsolXLData To run the consolidation Double-click the ConsolXLData macro sheet To view the consolidated data, double click on the tblConsolData table (If you don't see your data...STOP and we'll figure out what needs to be adjusted) Save and close the Access database (I'll assume it's called MyData.mdb) Using Excel: Select a blank sheet Data|Import External Data|Import Data Browse to the MSA mdb file using the Look In dropdown at the top of the window. When you find the file, double-click it to see the list of tables Double-click the tblConsolData table Select where in the Excel file you want to imported data to start Click the [OK] button....That should bring in your consolidated data Any other time you want to refresh the data: Right-click on one of the column headings in the Excel data range Select Refresh Data Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Yes if it's possible to retrieve the consolidate table from access to excel again... why not. The problem is dat I've never open a doc in access, but if you say I don't need a lot of experience in access I'm ready to do so... Can you guide me? "Ron Coderre" wrote: The way your data is structured does not lend itself to being easily consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
consolidation of tables in excel with text and figures
OK...let's see what I can do to help.
External table is not in the expected format<< Since access works with tables, your data must be in a table format. That means avoid blank column headings, spaces in column headings (MyCol....not: My Col), etc. Also, since it appeared that data columns might contain either numbers or text, I had you define each column as text. One way to help find the problem is to manually try to import the data into the MSA table: Start by opening your consolidation table Edit|Select All Records Press the [Delete] key to clear any data that might already be in the table File|Get External Data|Import Files of type: Excel files Select your file Click the [Import] button Select: Show Named Ranges Select a range name to import....Click [Next] Check: First rows contains Column Headings....Click [Next] Check: In an existing table...select your consolidation table....Click [Next] Click the [Finish] button If you run into any problems you can't figure out, note the error and post it. Repeat for each range i was not sure about the file name<< On the macro sheet, you enter the complete path of the file, including the name: example: "C:\myFolder\MyDataFile.xls" Since the sheets are in different workbooks, there will be a different file path and name for each line of the macro. Post your progress. Once you get comfortable with this method you'll see that it's extremely powerful. I've used it to consolidate literally hundreds of Excel tables in hardly any time at all. *********** Best Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks a lot for your clear help !! However, I have a problem when running the macro. it says "External table is not in the expected format". So i was wondering what i did wrong ... (to be honest i don't know) but i was wondering if the macro is retriving the data from all my workbook or do i need to put allmy sheet in one excelworkbook? Also i was no sure about the file name: to you meant something like that: "C:\MSLM test\Mars sheets for consolidation 1" the "Mars Sheets for consolidation1" beeing the folder where all the excel workbooks are. thks a lot brgds, florence "Ron Coderre" wrote: OK....Here you go Note: There's a bit of prep work to set things up, but you only have to do this once. Using your example data First, create a range for each set of data Here's how: Select the data range InsertNameDefine Names in Workbook: (enter a unique name here like: rngDataSht1) Refers to: (your already selected range) Click the [OK] button repeat for each sheet of data, changing the name each time: rngDataSht2, rngDataSht3, etc Save the file In MS Access (MSA), select the Tables tab Click the [New] button and use design mode Enter these fields (as text fields): Liner AB AC AD BC BD Save the table structure Name:tblConsolData (Do not set a primary key) Now, select the Macros tab Click the [New] button In the Action column select TransferSpreadsheet from the dropdown list. At the bottom of the window fill out the table as follows: Transfer type: Import Spreadsheet type: (use the default for Excel) Table Name: tblConsolData File Name: (Enter the complete path to the file, including the file name) Has Field Names: Yes Range: rngDataSht1 (or whatever name you used) Next, select the black triangle at the top of the window to select that row Edit|Copy Select the next row down Edit|Paste (to set commands to pull in the next data range) Switch to the bottom of the window and set the next range name to be pulled (Repeat for as many ranges as you need.) Save and close the macro sheet as: ConsolXLData To run the consolidation Double-click the ConsolXLData macro sheet To view the consolidated data, double click on the tblConsolData table (If you don't see your data...STOP and we'll figure out what needs to be adjusted) Save and close the Access database (I'll assume it's called MyData.mdb) Using Excel: Select a blank sheet Data|Import External Data|Import Data Browse to the MSA mdb file using the Look In dropdown at the top of the window. When you find the file, double-click it to see the list of tables Double-click the tblConsolData table Select where in the Excel file you want to imported data to start Click the [OK] button....That should bring in your consolidated data Any other time you want to refresh the data: Right-click on one of the column headings in the Excel data range Select Refresh Data Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Yes if it's possible to retrieve the consolidate table from access to excel again... why not. The problem is dat I've never open a doc in access, but if you say I don't need a lot of experience in access I'm ready to do so... Can you guide me? "Ron Coderre" wrote: The way your data is structured does not lend itself to being easily consolidated in Excel. Not that it couldn't be done but in this instance, Excel is just the wrong tool. Personally, I'd use MS Access to perform the consolidation. All you'd need to do is set up a table structure that contains all unique column headings, then import(append) each of the 20 data ranges to that table. Each Excel column of data would find its match in the MS Access table and automatically load there. To make the load process even easier you could quickly set up a macro sheet (not vba...a macro sheeet) to perform each load. In the MSA model I threw together using your sample data, the model consolidated everything just the way you want it in less than a blink. Even if you have very little experience with MS Access, that process would barely even touch its capabilities (not even queries). You'd only be using it as a staging ground. Is that something you'd consider? Regards, Ron *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hi Ron, thks for your answer. i'll take a example to make it easier to explain. sheet 1: liner ab ac ad 4 usd 234 456 5 eur 234 456 6 eur 234 456 sheet 2 Liner ab bc bd 1 741 789 258 2 741 789 258 3 741 789 258 consolidate sheet should be: Liner ab ac ad bc bd 4 usd 234 456 5 eur 234 456 6 eur 234 456 1 741 789 258 2 741 789 258 3 741 789 258 As you said, i would like to have all possible column headings and the appropriate data to align under each heading. as you can see from the example none of the rows are the same, so one cell can never contain more than one figure. With the consolidation tool it is easy to do but my text doesn't appear as you said in your answer.. The problem is that i would like to consolidate about 20 differents sheets (approximatly A1:BZ35) and i would like to you an easy formule/tool because it's something i would have to do continually in my job. It could be so nice if you could help be. thks, Flo "Ron Coderre" wrote: When you use Data Consolidation in Excel, it only uses the top row and left column as references to buld the consolidation. Consequently, if you have text in Col_A and Col_B and values in the other columns, you'll lose whatever is in Col_B. There are other alternatives, but we (ok...I ) would need to know what the rules are. You say "columns are not always the same". Since that means you couldn't just copy/paste the ranges, how are you hoping to consolidate? Do you want all possible column headings and the appropriate data to align under each heading? Would there be any summarization of like items? *********** Regards, Ron XL2002, WinXP-Pro "samenvoegen van sheets" wrote: Hello, I have to consolidate about 20 sheets from 20 differents workbooks (always the first one of a workbook). The amount of columns are not always the same, so i have used the "consolidation" option. The problem is that with the consolidation tool the text does not appear on the consolidate sheet. what did i do wrong? Or is there an other way to do so? thanks in advance Florence |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consolidation of tables in excel with text and figures | Excel Discussion (Misc queries) | |||
Formula to strip figures from cells text strings | Excel Discussion (Misc queries) | |||
How do I convert figures to text? | Excel Worksheet Functions |