|
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" 级糶秎ン穝籇 ... 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" 级糶秎ン穝籇 ... 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" wrote in message ... Thanks all of you, but the data is in the form of 200 separate tables in 200 separate files. "hkappleorange" 级糶秎ン穝籇 ... 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" 级糶秎ン穝籇 ... 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<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..... |
Thanks. I have reciewed the code it is kinda difficult for me as I am not
familiar with codes ... Thanks "hkappleorange" 级糶秎ン穝籇 ... 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" 级糶秎ン穝籇 ... 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..... |
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" 级糶秎ン穝籇 ... 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..... |
| All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com