how to merge data from numerous worksheets into one sheet.

[col]

Hi,I'm trying to merge data from numerous worksheets, with varying
numbers of rows, but the same column headings, into the one worksheet.
i've been cutting and pasting to get the desired results but it takes
at least 15 minutes per workbook.
Does anyone know if this is feasible? (Skill level?, I would say I'm no
more than a casual user)


Thanks in advance.

 

[Bryan Hessey]

What specifically is meant by 'merge data'

To do complete rows, filter the book to be copied and Select, Copy and
Paste as one selection,
To do matching cell data, use VLookup for the columns required.

Any further clues?

--

 

[Ron de Bruin]

Hi col

You can try this
http://www.rondebruin.nl/copy2.htm

 

[Arvi Laanemets]

Hi

One possible approach:
You estimate a max number of rows in any source table of all workbooks.
Let's it be <100 for example.
Create a sheet Links, with table headers in 1st row;
Into cell A2, create a link to cell A2 in 1st source table. P.e. when source
workbook is WB1 in C:\My Documents\Data, and source sheet is Data, then the
link formula will be:
=IF('C:\My Documents\Data\[WB1.xls]Data'!A2="","",'C:\My
Documents\Data\[WB1.xls]Data'!A2)
Copy the formula to whole table until row number 100
Into cell A101 enter similar link to cell A2 in another source table, etc.
until all source tables are linked.

In this way, you get a single table with all source table data in it.
Probably you has've to add an additional column, to identify to which source
the row belongs.

With such table you are able to perform various calculations, but it has a
lot of gaps in it. To get rid of them you have 2 options:
1) Use autofilter to display only non-empty rows.
2) Hide the sheet Links, define the table on it as a named range, and use
ODBC query to get data without empty rows to another sheet. You can set the
query to be refreshed every time the workbook is opened, and/or after some
time interval when workbook remains open.


Arvi Laanemets

 

[col]

Hi ron, thanks for your help, really appreciate it. The link did
exactly what I was wanting.

 

[col]

Hi ron, thanks for your help, really appreciate it. The link did
exactly what I was wanting.

 

[col]

Brian / Arvi, thanks for your input. Rons link did the trick.