Hi
"hkappleorange" wrote in message
...
Thanks a lot. I think your solution suits me as there is no code. What
should those parameters be ???
It depends on your data - i.e. why are all those data in 200 different
files. P.e. how are you p.e. deciding, that data in 2 different files are
different data, and that you don't have there 2 copies of same file you
created occassionally? The Parameter can be or agent name, or product, or
region or month (last 2 possibilities reduce the number of columns in Links
table to 3), or whatever other value by with data are collected into
different files. It can even be missing (when you simply entered some
portion of data into file, then decided that it's enough, and started a new
one) - then you can use file name as parameter (and scrap the Files sheet).
Arvi Laanemets
"Arvi Laanemets" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
How are data grouped into all those 200 files - is there some 4th
parameter,
or are those files for monthly data, or region data?
Let's assume you have some 4th parameter (Parameter) by which data are
grouped to files (when not, then the solution will be simplified). And
that
in all files data are on sheet Data.
Create a sheet Links with headers in row 1: Parameter, Region, Month,
Amount.
Create a sheet Files with table Parameter, File. Enter into this table
all
filenames, when all files are in same folder, or filenames (enclosed
into
square brackets) preceeded with full path, when they aren't in single
folder. For every filename, enter Parameter. You get a table with data
in
range A2:B201
On sheet Links, into cell A2 enter the first parameter from sheet Files,
and
copy it down to A301. Into A302 enter the 2nd parameter from sheet
Files,
and copy it down to A601, etc. until all 200 parameters have 300 rows in
table Links. When you didn't enter full paths along with file names (all
files were in single folder), enter this path into some cell on sheet
Links,
p.e. E1.
Into row2 enter formulas like (this one is for case the path is entered
into
cell E1)
B2: ="='" & $E$1 & "[" & A2 & "]Data'!A" & ROW(B2)
C2: ="='" & $E$1 & "[" & A2 & "]Data'!B" & ROW(B2)
D2: ="='" & $E41 & "[" & A2 & "]Data'!C" & ROW(B2)
Copy those formulas to range B2:D302.
In row 302, edit formulas to be like
B302: ="='" & $E$1 & "[" & A302 & "]Data'!A" & ROW(B2)
C302: ="='" & $E$1 & "[" & A302 & "]Data'!B" & ROW(B2)
D302: ="='" & $E$1 & "[" & A302 & "]Data'!C" & ROW(B2)
, and copy those formulas to range B302:D602
etc. for all 300-row groups.
Select the whole range with formulas on sheet Links (B2:D30002 ?), and
copy
them. Then PasteSpecial Values, and after that replace all "=" with "=".
When all was done properly, you get working links to all 200 files (The
formulas above were typed on fly, you have to check them. And maybe you
have
to include the check for empty cell into link formula too {so the result
formula will be something like =IF(Link="","",Link)}.
Whe you get all links working, define a fixed named range, p.e.
SourceData,
as
=Links!$A$1:$D$30002
(adjust the range to your real table).
Save the workbook. Now on another sheet, select A1, and create an ODBC
query
from Excel table. Give your workbook as datasource, and the named range
(SourceData) as table. Set the condition for column Region to Not Null,
and
finish. You get the table without gaps.
--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )
"hkappleorange" wrote in message
...
I have a simple task on Excel but I cannot figure it out. Pls help:-
I have 200 separate Excel files with data all in 3 columns: Region,
Month
&
Amount. They each has a range of records from 100 to 300 lines each.
I am asked to create a single 3 column table with these 3 fields
containing
all data from these 200 tables in separate files.
How many ways we can do this ? Please help.....
|