software

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
quot;consolidationquot; 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
FlorenceWhen 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 quot;columns are not always the samequot;. 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-Proquot;samenvoegen van sheetsquot; wrote:

gt; Hello,
gt;
gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; the first one of a workbook).
gt; The amount of columns are not always the same, so i have used the
gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; text does not appear on the consolidate sheet.
gt;
gt; what did i do wrong? Or is there an other way to do so?
gt;
gt; thanks in advance
gt; Florence
gt;

Hi Ron,

thks for your answer.
i'll take a example to make it easier to explain.
sheet 1:
liner ab acad
4usd234456
5eur234456
6eur234456

sheet 2
Linerab bcbd
1741789258
2741789258
3741789258

consolidate sheet should be:
Linerab acadbcbd
4usd234456
5eur234456
6eur234456
1741789258
2741789258
3741789258

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,
Floquot;Ron Coderrequot; wrote:

gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; column as references to buld the consolidation. Consequently, if you have
gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; is in Col_B.
gt;
gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; you want all possible column headings and the appropriate data to align under
gt; each heading? Would there be any summarization of like items?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;samenvoegen van sheetsquot; wrote:
gt;
gt; gt; Hello,
gt; gt;
gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; the first one of a workbook).
gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; text does not appear on the consolidate sheet.
gt; gt;
gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt;
gt; gt; thanks in advance
gt; gt; Florence
gt; gt;

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-Proquot;samenvoegen van sheetsquot; wrote:

gt; Hi Ron,
gt;
gt; thks for your answer.
gt; i'll take a example to make it easier to explain.
gt; sheet 1:
gt; liner ab acad
gt; 4usd234456
gt; 5eur234456
gt; 6eur234456
gt;
gt; sheet 2
gt; Linerab bcbd
gt; 1741789258
gt; 2741789258
gt; 3741789258
gt;
gt; consolidate sheet should be:
gt; Linerab acadbcbd
gt; 4usd234456
gt; 5eur234456
gt; 6eur234456
gt; 1741789258
gt; 2741789258
gt; 3741789258
gt;
gt; As you said, i would like to have all possible column headings and the
gt; appropriate data to align under each heading.
gt; as you can see from the example none of the rows are the same, so one cell
gt; can never contain more than one figure.
gt; With the consolidation tool it is easy to do but my text doesn't appear as
gt; you said in your answer..
gt; The problem is that i would like to consolidate about 20 differents sheets
gt; (approximatly A1:BZ35) and i would like to you an easy formule/tool because
gt; it's something i would have to do continually in my job.
gt;
gt; It could be so nice if you could help be.
gt; thks,
gt; Flo
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; gt; column as references to buld the consolidation. Consequently, if you have
gt; gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; gt; is in Col_B.
gt; gt;
gt; gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; gt; you want all possible column headings and the appropriate data to align under
gt; gt; each heading? Would there be any summarization of like items?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt;
gt; gt; gt; Hello,
gt; gt; gt;
gt; gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; gt; the first one of a workbook).
gt; gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; gt; text does not appear on the consolidate sheet.
gt; gt; gt;
gt; gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt; gt;
gt; gt; gt; thanks in advance
gt; gt; gt; Florence
gt; gt; gt;

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?

quot;Ron Coderrequot; wrote:

gt; The way your data is structured does not lend itself to being easily
gt; consolidated in Excel. Not that it couldn't be done but in this instance,
gt; Excel is just the wrong tool.
gt;
gt; Personally, I'd use MS Access to perform the consolidation.
gt; All you'd need to do is set up a table structure that contains all unique
gt; column headings, then import(append) each of the 20 data ranges to that
gt; table. Each Excel column of data would find its match in the MS Access table
gt; and automatically load there. To make the load process even easier you could
gt; quickly set up a macro sheet (not vba...a macro sheeet) to perform each load.
gt; In the MSA model I threw together using your sample data, the model
gt; consolidated everything just the way you want it in less than a blink.
gt;
gt; Even if you have very little experience with MS Access, that process would
gt; barely even touch its capabilities (not even queries). You'd only be using
gt; it as a staging ground.
gt;
gt; Is that something you'd consider?
gt;
gt; Regards,
gt; Ron
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;samenvoegen van sheetsquot; wrote:
gt;
gt; gt; Hi Ron,
gt; gt;
gt; gt; thks for your answer.
gt; gt; i'll take a example to make it easier to explain.
gt; gt; sheet 1:
gt; gt; liner ab acad
gt; gt; 4usd234456
gt; gt; 5eur234456
gt; gt; 6eur234456
gt; gt;
gt; gt; sheet 2
gt; gt; Linerab bcbd
gt; gt; 1741789258
gt; gt; 2741789258
gt; gt; 3741789258
gt; gt;
gt; gt; consolidate sheet should be:
gt; gt; Linerab acadbcbd
gt; gt; 4usd234456
gt; gt; 5eur234456
gt; gt; 6eur234456
gt; gt; 1741789258
gt; gt; 2741789258
gt; gt; 3741789258
gt; gt;
gt; gt; As you said, i would like to have all possible column headings and the
gt; gt; appropriate data to align under each heading.
gt; gt; as you can see from the example none of the rows are the same, so one cell
gt; gt; can never contain more than one figure.
gt; gt; With the consolidation tool it is easy to do but my text doesn't appear as
gt; gt; you said in your answer..
gt; gt; The problem is that i would like to consolidate about 20 differents sheets
gt; gt; (approximatly A1:BZ35) and i would like to you an easy formule/tool because
gt; gt; it's something i would have to do continually in my job.
gt; gt;
gt; gt; It could be so nice if you could help be.
gt; gt; thks,
gt; gt; Flo
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; gt; gt; column as references to buld the consolidation. Consequently, if you have
gt; gt; gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; gt; gt; is in Col_B.
gt; gt; gt;
gt; gt; gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; gt; gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; gt; gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; gt; gt; you want all possible column headings and the appropriate data to align under
gt; gt; gt; each heading? Would there be any summarization of like items?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hello,
gt; gt; gt; gt;
gt; gt; gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; gt; gt; the first one of a workbook).
gt; gt; gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; gt; gt; text does not appear on the consolidate sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt; gt; gt;
gt; gt; gt; gt; thanks in advance
gt; gt; gt; gt; Florence
gt; gt; gt; gt;

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
Insertgt;Namegt;Define
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-Proquot;samenvoegen van sheetsquot; wrote:

gt; Yes if it's possible to retrieve the consolidate table from access to excel
gt; again... why not.
gt; The problem is dat I've never open a doc in access, but if you say I don't
gt; need a lot of experience in access I'm ready to do so...
gt; Can you guide me?
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; The way your data is structured does not lend itself to being easily
gt; gt; consolidated in Excel. Not that it couldn't be done but in this instance,
gt; gt; Excel is just the wrong tool.
gt; gt;
gt; gt; Personally, I'd use MS Access to perform the consolidation.
gt; gt; All you'd need to do is set up a table structure that contains all unique
gt; gt; column headings, then import(append) each of the 20 data ranges to that
gt; gt; table. Each Excel column of data would find its match in the MS Access table
gt; gt; and automatically load there. To make the load process even easier you could
gt; gt; quickly set up a macro sheet (not vba...a macro sheeet) to perform each load.
gt; gt; In the MSA model I threw together using your sample data, the model
gt; gt; consolidated everything just the way you want it in less than a blink.
gt; gt;
gt; gt; Even if you have very little experience with MS Access, that process would
gt; gt; barely even touch its capabilities (not even queries). You'd only be using
gt; gt; it as a staging ground.
gt; gt;
gt; gt; Is that something you'd consider?
gt; gt;
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt;
gt; gt; gt; Hi Ron,
gt; gt; gt;
gt; gt; gt; thks for your answer.
gt; gt; gt; i'll take a example to make it easier to explain.
gt; gt; gt; sheet 1:
gt; gt; gt; liner ab acad
gt; gt; gt; 4usd234456
gt; gt; gt; 5eur234456
gt; gt; gt; 6eur234456
gt; gt; gt;
gt; gt; gt; sheet 2
gt; gt; gt; Linerab bcbd
gt; gt; gt; 1741789258
gt; gt; gt; 2741789258
gt; gt; gt; 3741789258
gt; gt; gt;
gt; gt; gt; consolidate sheet should be:
gt; gt; gt; Linerab acadbcbd
gt; gt; gt; 4usd234456
gt; gt; gt; 5eur234456
gt; gt; gt; 6eur234456
gt; gt; gt; 1741789258
gt; gt; gt; 2741789258
gt; gt; gt; 3741789258
gt; gt; gt;
gt; gt; gt; As you said, i would like to have all possible column headings and the
gt; gt; gt; appropriate data to align under each heading.
gt; gt; gt; as you can see from the example none of the rows are the same, so one cell
gt; gt; gt; can never contain more than one figure.
gt; gt; gt; With the consolidation tool it is easy to do but my text doesn't appear as
gt; gt; gt; you said in your answer..
gt; gt; gt; The problem is that i would like to consolidate about 20 differents sheets
gt; gt; gt; (approximatly A1:BZ35) and i would like to you an easy formule/tool because
gt; gt; gt; it's something i would have to do continually in my job.
gt; gt; gt;
gt; gt; gt; It could be so nice if you could help be.
gt; gt; gt; thks,
gt; gt; gt; Flo
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; gt; gt; gt; column as references to buld the consolidation. Consequently, if you have
gt; gt; gt; gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; gt; gt; gt; is in Col_B.
gt; gt; gt; gt;
gt; gt; gt; gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; gt; gt; gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; gt; gt; gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; gt; gt; gt; you want all possible column headings and the appropriate data to align under
gt; gt; gt; gt; each heading? Would there be any summarization of like items?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; gt; gt; gt; the first one of a workbook).
gt; gt; gt; gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; gt; gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; gt; gt; gt; text does not appear on the consolidate sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; thanks in advance
gt; gt; gt; gt; gt; Florence
gt; gt; gt; gt; gt;

Hi Ron,
thks a lot for your clear help !!
However, I have a problem when running the macro.
it says quot;External table is not in the expected formatquot;.

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:
quot;C:\MSLM test\Mars sheets for consolidation 1quot;
the quot;Mars Sheets for consolidation1quot; beeing the folder where all the excel
workbooks are.

thks a lot
brgds,
florence

quot;Ron Coderrequot; wrote:

gt; OK....Here you go
gt;
gt; Note: There's a bit of prep work to set things up, but you only have to do
gt; this once.
gt;
gt; Using your example data
gt;
gt; First, create a range for each set of data
gt; Here's how:
gt; Select the data range
gt; Insertgt;Namegt;Define
gt; Names in Workbook: (enter a unique name here like: rngDataSht1)
gt; Refers to: (your already selected range)
gt; Click the [OK] button
gt; repeat for each sheet of data, changing the name each time:
gt; rngDataSht2, rngDataSht3, etc
gt;
gt; Save the file
gt;
gt; In MS Access (MSA), select the Tables tab
gt; Click the [New] button and use design mode
gt; Enter these fields (as text fields):
gt; Liner
gt; AB
gt; AC
gt; AD
gt; BC
gt; BD
gt;
gt; Save the table structure
gt; Name:tblConsolData
gt; (Do not set a primary key)
gt;
gt; Now, select the Macros tab
gt; Click the [New] button
gt; In the Action column select TransferSpreadsheet from the dropdown list.
gt; At the bottom of the window fill out the table as follows:
gt; Transfer type: Import
gt; Spreadsheet type: (use the default for Excel)
gt; Table Name: tblConsolData
gt; File Name: (Enter the complete path to the file, including the file name)
gt; Has Field Names: Yes
gt; Range: rngDataSht1 (or whatever name you used)
gt;
gt; Next, select the black triangle at the top of the window to select that row
gt; Edit|Copy
gt; Select the next row down
gt; Edit|Paste (to set commands to pull in the next data range)
gt; Switch to the bottom of the window and set the next range name to be pulled
gt; (Repeat for as many ranges as you need.)
gt;
gt; Save and close the macro sheet as: ConsolXLData
gt;
gt; To run the consolidation
gt; Double-click the ConsolXLData macro sheet
gt;
gt; To view the consolidated data, double click on the tblConsolData table
gt; (If you don't see your data...STOP and we'll figure out what needs to be
gt; adjusted)
gt; Save and close the Access database (I'll assume it's called MyData.mdb)
gt;
gt; Using Excel:
gt; Select a blank sheet
gt; Data|Import External Data|Import Data
gt; Browse to the MSA mdb file using the Look In dropdown at the top of the
gt; window.
gt; When you find the file, double-click it to see the list of tables
gt; Double-click the tblConsolData table
gt; Select where in the Excel file you want to imported data to start
gt; Click the [OK] button....That should bring in your consolidated data
gt;
gt; Any other time you want to refresh the data:
gt; Right-click on one of the column headings in the Excel data range
gt; Select Refresh Data
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;samenvoegen van sheetsquot; wrote:
gt;
gt; gt; Yes if it's possible to retrieve the consolidate table from access to excel
gt; gt; again... why not.
gt; gt; The problem is dat I've never open a doc in access, but if you say I don't
gt; gt; need a lot of experience in access I'm ready to do so...
gt; gt; Can you guide me?
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; The way your data is structured does not lend itself to being easily
gt; gt; gt; consolidated in Excel. Not that it couldn't be done but in this instance,
gt; gt; gt; Excel is just the wrong tool.
gt; gt; gt;
gt; gt; gt; Personally, I'd use MS Access to perform the consolidation.
gt; gt; gt; All you'd need to do is set up a table structure that contains all unique
gt; gt; gt; column headings, then import(append) each of the 20 data ranges to that
gt; gt; gt; table. Each Excel column of data would find its match in the MS Access table
gt; gt; gt; and automatically load there. To make the load process even easier you could
gt; gt; gt; quickly set up a macro sheet (not vba...a macro sheeet) to perform each load.
gt; gt; gt; In the MSA model I threw together using your sample data, the model
gt; gt; gt; consolidated everything just the way you want it in less than a blink.
gt; gt; gt;
gt; gt; gt; Even if you have very little experience with MS Access, that process would
gt; gt; gt; barely even touch its capabilities (not even queries). You'd only be using
gt; gt; gt; it as a staging ground.
gt; gt; gt;
gt; gt; gt; Is that something you'd consider?
gt; gt; gt;
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi Ron,
gt; gt; gt; gt;
gt; gt; gt; gt; thks for your answer.
gt; gt; gt; gt; i'll take a example to make it easier to explain.
gt; gt; gt; gt; sheet 1:
gt; gt; gt; gt; liner ab acad
gt; gt; gt; gt; 4usd234456
gt; gt; gt; gt; 5eur234456
gt; gt; gt; gt; 6eur234456
gt; gt; gt; gt;
gt; gt; gt; gt; sheet 2
gt; gt; gt; gt; Linerab bcbd
gt; gt; gt; gt; 1741789258
gt; gt; gt; gt; 2741789258
gt; gt; gt; gt; 3741789258
gt; gt; gt; gt;
gt; gt; gt; gt; consolidate sheet should be:
gt; gt; gt; gt; Linerab acadbcbd
gt; gt; gt; gt; 4usd234456
gt; gt; gt; gt; 5eur234456
gt; gt; gt; gt; 6eur234456
gt; gt; gt; gt; 1741789258
gt; gt; gt; gt; 2741789258
gt; gt; gt; gt; 3741789258
gt; gt; gt; gt;
gt; gt; gt; gt; As you said, i would like to have all possible column headings and the
gt; gt; gt; gt; appropriate data to align under each heading.
gt; gt; gt; gt; as you can see from the example none of the rows are the same, so one cell
gt; gt; gt; gt; can never contain more than one figure.
gt; gt; gt; gt; With the consolidation tool it is easy to do but my text doesn't appear as
gt; gt; gt; gt; you said in your answer..
gt; gt; gt; gt; The problem is that i would like to consolidate about 20 differents sheets
gt; gt; gt; gt; (approximatly A1:BZ35) and i would like to you an easy formule/tool because
gt; gt; gt; gt; it's something i would have to do continually in my job.
gt; gt; gt; gt;
gt; gt; gt; gt; It could be so nice if you could help be.
gt; gt; gt; gt; thks,
gt; gt; gt; gt; Flo
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; gt; gt; gt; gt; column as references to buld the consolidation. Consequently, if you have
gt; gt; gt; gt; gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; gt; gt; gt; gt; is in Col_B.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; gt; gt; gt; gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; gt; gt; gt; gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; gt; gt; gt; gt; you want all possible column headings and the appropriate data to align under
gt; gt; gt; gt; gt; each heading? Would there be any summarization of like items?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; gt; gt; gt; gt; the first one of a workbook).
gt; gt; gt; gt; gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; gt; gt; gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; gt; gt; gt; gt; text does not appear on the consolidate sheet.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; thanks in advance
gt; gt; gt; gt; gt; gt; Florence
gt; gt; gt; gt; gt; gt;

Ok thks for your help i'll try to verify all this and then i'll try again.
I'll keep you informed

quot;Ron Coderrequot; wrote:

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

OK...let's see what I can do to help.

gt;gt;External table is not in the expected formatlt;lt;
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

gt;gt;i was not sure about the file namelt;lt;
On the macro sheet, you enter the complete path of the file, including the
name:
example: quot;C:\myFolder\MyDataFile.xlsquot;

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-Proquot;samenvoegen van sheetsquot; wrote:

gt; Hi Ron,
gt; thks a lot for your clear help !!
gt; However, I have a problem when running the macro.
gt; it says quot;External table is not in the expected formatquot;.
gt;
gt; So i was wondering what i did wrong ... (to be honest i don't know)
gt; but i was wondering if the macro is retriving the data from all my workbook
gt; or do i need to put allmy sheet in one excelworkbook?
gt;
gt; Also i was no sure about the file name: to you meant something like that:
gt; quot;C:\MSLM test\Mars sheets for consolidation 1quot;
gt; the quot;Mars Sheets for consolidation1quot; beeing the folder where all the excel
gt; workbooks are.
gt;
gt; thks a lot
gt; brgds,
gt; florence
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; OK....Here you go
gt; gt;
gt; gt; Note: There's a bit of prep work to set things up, but you only have to do
gt; gt; this once.
gt; gt;
gt; gt; Using your example data
gt; gt;
gt; gt; First, create a range for each set of data
gt; gt; Here's how:
gt; gt; Select the data range
gt; gt; Insertgt;Namegt;Define
gt; gt; Names in Workbook: (enter a unique name here like: rngDataSht1)
gt; gt; Refers to: (your already selected range)
gt; gt; Click the [OK] button
gt; gt; repeat for each sheet of data, changing the name each time:
gt; gt; rngDataSht2, rngDataSht3, etc
gt; gt;
gt; gt; Save the file
gt; gt;
gt; gt; In MS Access (MSA), select the Tables tab
gt; gt; Click the [New] button and use design mode
gt; gt; Enter these fields (as text fields):
gt; gt; Liner
gt; gt; AB
gt; gt; AC
gt; gt; AD
gt; gt; BC
gt; gt; BD
gt; gt;
gt; gt; Save the table structure
gt; gt; Name:tblConsolData
gt; gt; (Do not set a primary key)
gt; gt;
gt; gt; Now, select the Macros tab
gt; gt; Click the [New] button
gt; gt; In the Action column select TransferSpreadsheet from the dropdown list.
gt; gt; At the bottom of the window fill out the table as follows:
gt; gt; Transfer type: Import
gt; gt; Spreadsheet type: (use the default for Excel)
gt; gt; Table Name: tblConsolData
gt; gt; File Name: (Enter the complete path to the file, including the file name)
gt; gt; Has Field Names: Yes
gt; gt; Range: rngDataSht1 (or whatever name you used)
gt; gt;
gt; gt; Next, select the black triangle at the top of the window to select that row
gt; gt; Edit|Copy
gt; gt; Select the next row down
gt; gt; Edit|Paste (to set commands to pull in the next data range)
gt; gt; Switch to the bottom of the window and set the next range name to be pulled
gt; gt; (Repeat for as many ranges as you need.)
gt; gt;
gt; gt; Save and close the macro sheet as: ConsolXLData
gt; gt;
gt; gt; To run the consolidation
gt; gt; Double-click the ConsolXLData macro sheet
gt; gt;
gt; gt; To view the consolidated data, double click on the tblConsolData table
gt; gt; (If you don't see your data...STOP and we'll figure out what needs to be
gt; gt; adjusted)
gt; gt; Save and close the Access database (I'll assume it's called MyData.mdb)
gt; gt;
gt; gt; Using Excel:
gt; gt; Select a blank sheet
gt; gt; Data|Import External Data|Import Data
gt; gt; Browse to the MSA mdb file using the Look In dropdown at the top of the
gt; gt; window.
gt; gt; When you find the file, double-click it to see the list of tables
gt; gt; Double-click the tblConsolData table
gt; gt; Select where in the Excel file you want to imported data to start
gt; gt; Click the [OK] button....That should bring in your consolidated data
gt; gt;
gt; gt; Any other time you want to refresh the data:
gt; gt; Right-click on one of the column headings in the Excel data range
gt; gt; Select Refresh Data
gt; gt;
gt; gt; Is that something you can work with?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt;
gt; gt; gt; Yes if it's possible to retrieve the consolidate table from access to excel
gt; gt; gt; again... why not.
gt; gt; gt; The problem is dat I've never open a doc in access, but if you say I don't
gt; gt; gt; need a lot of experience in access I'm ready to do so...
gt; gt; gt; Can you guide me?
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; The way your data is structured does not lend itself to being easily
gt; gt; gt; gt; consolidated in Excel. Not that it couldn't be done but in this instance,
gt; gt; gt; gt; Excel is just the wrong tool.
gt; gt; gt; gt;
gt; gt; gt; gt; Personally, I'd use MS Access to perform the consolidation.
gt; gt; gt; gt; All you'd need to do is set up a table structure that contains all unique
gt; gt; gt; gt; column headings, then import(append) each of the 20 data ranges to that
gt; gt; gt; gt; table. Each Excel column of data would find its match in the MS Access table
gt; gt; gt; gt; and automatically load there. To make the load process even easier you could
gt; gt; gt; gt; quickly set up a macro sheet (not vba...a macro sheeet) to perform each load.
gt; gt; gt; gt; In the MSA model I threw together using your sample data, the model
gt; gt; gt; gt; consolidated everything just the way you want it in less than a blink.
gt; gt; gt; gt;
gt; gt; gt; gt; Even if you have very little experience with MS Access, that process would
gt; gt; gt; gt; barely even touch its capabilities (not even queries). You'd only be using
gt; gt; gt; gt; it as a staging ground.
gt; gt; gt; gt;
gt; gt; gt; gt; Is that something you'd consider?
gt; gt; gt; gt;
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi Ron,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; thks for your answer.
gt; gt; gt; gt; gt; i'll take a example to make it easier to explain.
gt; gt; gt; gt; gt; sheet 1:
gt; gt; gt; gt; gt; liner ab acad
gt; gt; gt; gt; gt; 4usd234456
gt; gt; gt; gt; gt; 5eur234456
gt; gt; gt; gt; gt; 6eur234456
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; sheet 2
gt; gt; gt; gt; gt; Linerab bcbd
gt; gt; gt; gt; gt; 1741789258
gt; gt; gt; gt; gt; 2741789258
gt; gt; gt; gt; gt; 3741789258
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; consolidate sheet should be:
gt; gt; gt; gt; gt; Linerab acadbcbd
gt; gt; gt; gt; gt; 4usd234456
gt; gt; gt; gt; gt; 5eur234456
gt; gt; gt; gt; gt; 6eur234456
gt; gt; gt; gt; gt; 1741789258
gt; gt; gt; gt; gt; 2741789258
gt; gt; gt; gt; gt; 3741789258
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; As you said, i would like to have all possible column headings and the
gt; gt; gt; gt; gt; appropriate data to align under each heading.
gt; gt; gt; gt; gt; as you can see from the example none of the rows are the same, so one cell
gt; gt; gt; gt; gt; can never contain more than one figure.
gt; gt; gt; gt; gt; With the consolidation tool it is easy to do but my text doesn't appear as
gt; gt; gt; gt; gt; you said in your answer..
gt; gt; gt; gt; gt; The problem is that i would like to consolidate about 20 differents sheets
gt; gt; gt; gt; gt; (approximatly A1:BZ35) and i would like to you an easy formule/tool because
gt; gt; gt; gt; gt; it's something i would have to do continually in my job.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; It could be so nice if you could help be.
gt; gt; gt; gt; gt; thks,
gt; gt; gt; gt; gt; Flo
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; When you use Data Consolidation in Excel, it only uses the top row and left
gt; gt; gt; gt; gt; gt; column as references to buld the consolidation. Consequently, if you have
gt; gt; gt; gt; gt; gt; text in Col_A and Col_B and values in the other columns, you'll lose whatever
gt; gt; gt; gt; gt; gt; is in Col_B.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; There are other alternatives, but we (ok...I ) would need to know what the
gt; gt; gt; gt; gt; gt; rules are. You say quot;columns are not always the samequot;. Since that means you
gt; gt; gt; gt; gt; gt; couldn't just copy/paste the ranges, how are you hoping to consolidate? Do
gt; gt; gt; gt; gt; gt; you want all possible column headings and the appropriate data to align under
gt; gt; gt; gt; gt; gt; each heading? Would there be any summarization of like items?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;samenvoegen van sheetsquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have to consolidate about 20 sheets from 20 differents workbooks (always
gt; gt; gt; gt; gt; gt; gt; the first one of a workbook).
gt; gt; gt; gt; gt; gt; gt; The amount of columns are not always the same, so i have used the
gt; gt; gt; gt; gt; gt; gt; quot;consolidationquot; option. The problem is that with the consolidation tool the
gt; gt; gt; gt; gt; gt; gt; text does not appear on the consolidate sheet.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; what did i do wrong? Or is there an other way to do so?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; thanks in advance
gt; gt; gt; gt; gt; gt; gt; Florence
gt; gt; gt; gt; gt; gt; gt;