consolidation of tables in excel with text and figures

[samenvoegen van sheets]

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

[Ron Coderre]

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
>

[samenvoegen van sheets]

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
> >

[Ron Coderre]

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
> > >

[samenvoegen van sheets]

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
> > > >

[Ron Coderre]

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
Insert>Name>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-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
> > > > >

[samenvoegen van sheets]

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
> Insert>Name>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-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
> > > > > >

[samenvoegen van sheets]

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
> > > Insert>Name>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-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
> > > > > > > >

[Ron Coderre]

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
> > Insert>Name>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-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
> > > > > > >