EXCEL MISSION IMPOSSIBLE ?

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

Maybe you could use some code from Ron de Bruin:

http://www.rondebruin.nl/copy3.htm



hkappleorange wrote:
>
> 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.....

--

Dave Peterson

I'd copy and paste-values from source to destination, then mark up the source
files as obsolete (by re-naming them).

Alternatively, use formlae to link to the first source, fill down, find the
end of the data range, change the filename in the formulae, fill down, repeat
198 more times, then copy-paste-values to eliminate all of the links.

No quick way that I can think of, not in Excel at least.

If you use Access, you could try to import the spreadsheets into a
common-format database, then export to an Excel sheet. I don't use Access,
so I don't know how cumbersome that might be.


"hkappleorange" wrote:

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

if you only have to do it once
copy and paste may be the simplest

another way would be to use the indirect() function

if you have a list of all of the file and worksheet names
in column A (or A and B) insert the list(s) or workbooks and worksheets
You may have to play with them a bit to get the format proper for the
indirect() function

and if the max number of records is 300 in any file
copy your list and fill down so that you will have 300 duplicates of the list

Sort the columns
in B1 enter
=indirect(A1&"A"&mod(row(),300))
in C1 enter
=indirect(A1&"B"&mod(row(),300))
and in D1 enter
=indirect(A1&"C"&mod(row(),300))
And copy these and paste to the bottom of your list
Select columns B,C and D and copy and paste special values on top of
themselves.
Use autofilter and select blanks and delete to get rid of the extra rows.

you can also write a macro to do a copy and paste from all of the files.




"hkappleorange" wrote:

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

Thanks all of you, but the data is in the form of 200 separate tables in 200
separate files.

Anyone has a better solution ?? Help....


"bj" <[email protected]> 级糶秎ン穝籇
:[email protected]...
> if you only have to do it once
> copy and paste may be the simplest
>
> another way would be to use the indirect() function
>
> if you have a list of all of the file and worksheet names
> in column A (or A and B) insert the list(s) or workbooks and worksheets
> You may have to play with them a bit to get the format proper for the
> indirect() function
>
> and if the max number of records is 300 in any file
> copy your list and fill down so that you will have 300 duplicates of the
list
>
> Sort the columns
> in B1 enter
> =indirect(A1&"A"&mod(row(),300))
> in C1 enter
> =indirect(A1&"B"&mod(row(),300))
> and in D1 enter
> =indirect(A1&"C"&mod(row(),300))
> And copy these and paste to the bottom of your list
> Select columns B,C and D and copy and paste special values on top of
> themselves.
> Use autofilter and select blanks and delete to get rid of the extra rows.
>
> you can also write a macro to do a copy and paste from all of the files.
>
>
>
>
> "hkappleorange" wrote:
>
> > 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.....
> >
> >
> >
> >

Thanks all of you, but the data is in the form of 200 separate tables in 200
separate files.

"hkappleorange" <[email protected]> 级糶秎ン穝籇
:[email protected]...
> 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.....
>
>
>

Have you try the link that Dave posted ?
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"hkappleorange" <[email protected]> wrote in message news:%[email protected]...
> Thanks all of you, but the data is in the form of 200 separate tables in 200
> separate files.
>
> "hkappleorange" <[email protected]> 级糶秎ン穝籇
> :[email protected]...
>> 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.....
>>
>>
>>
>
>

Did you look at Ron's code?

hkappleorange wrote:
>
> Thanks all of you, but the data is in the form of 200 separate tables in 200
> separate files.
>
> Anyone has a better solution ?? Help....
>
> "bj" <[email protected]> 级糶秎ン穝籇
> :[email protected]...
> > if you only have to do it once
> > copy and paste may be the simplest
> >
> > another way would be to use the indirect() function
> >
> > if you have a list of all of the file and worksheet names
> > in column A (or A and B) insert the list(s) or workbooks and worksheets
> > You may have to play with them a bit to get the format proper for the
> > indirect() function
> >
> > and if the max number of records is 300 in any file
> > copy your list and fill down so that you will have 300 duplicates of the
> list
> >
> > Sort the columns
> > in B1 enter
> > =indirect(A1&"A"&mod(row(),300))
> > in C1 enter
> > =indirect(A1&"B"&mod(row(),300))
> > and in D1 enter
> > =indirect(A1&"C"&mod(row(),300))
> > And copy these and paste to the bottom of your list
> > Select columns B,C and D and copy and paste special values on top of
> > themselves.
> > Use autofilter and select blanks and delete to get rid of the extra rows.
> >
> > you can also write a macro to do a copy and paste from all of the files.
> >
> >
> >
> >
> > "hkappleorange" wrote:
> >
> > > 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.....
> > >
> > >
> > >
> > >

--

Dave Peterson

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<at>tarkon.ee )


"hkappleorange" <[email protected]> wrote in message
news:[email protected]...
>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.....
>
>
>

Thanks. I have reciewed the code it is kinda difficult for me as I am not
familiar with codes ...

Thanks
"hkappleorange" <[email protected]> 级糶秎ン穝籇
:[email protected]...
> 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.....
>
>
>

Thanks a lot. I think your solution suits me as there is no code. What
should those parameters be ???


"Arvi Laanemets" <[email protected]> 级糶秎ン穝籇
:[email protected]...
> 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<at>tarkon.ee )
>
>
> "hkappleorange" <[email protected]> wrote in message
> news:[email protected]...
> >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.....
> >
> >
> >
>
>

Hi


"hkappleorange" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 级糶秎ン穝籇
> :[email protected]...
> > 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<at>tarkon.ee )
> >
> >
> > "hkappleorange" <[email protected]> wrote in message
> > news:[email protected]...
> > >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.....
> > >
> > >
> > >
> >
> >
>
>