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