data processing

i am not sure what specific questions i want to ask but if you can read what
the situation is and help out that will be great.

I have about 400 excel files for my data, in each file, there are some rows
and columns i don't need and some spliting and calulations to do for the rows
and columns that i am interesed in. so~i assume it's a good idea to use MACRO.

say i have file A which contains 200 rows of data, file B which as 210, file
C which has 220 and so on. suppose i worked in file A to setup the macro.
well i got rid of the rows and columns i don't need, slipt the cells. i
assign a function to a cell, drag to apply it through the 200 rows (if i drag
to 300 will create trash data), finish setting up the macro. as everyone can
expect, when apply this macro to file B and file C, there are 10 and 20 rows
left over. so~ is there any way i can make excel to check the end of data and
apply functions accordingly?

also another questions, say i have 5 cells A1 to A5 all contains the data
"a", cell A6 to A10 al have the same data "b", and other columns has other
data. it there something allows be to move the rows start with "a" to a new
spredsheet called "a" and rows start with "b" to a new spredsheet called "b"?
again in these 400 files i have, the number of rows varies, so i will have no
idea how many rows will start with "a'.

just want to process these files in an efficient way.

thanks in advance,

Youyou

I doubt that anyone can give a proper answer to your question, and the tendancy would probably be to ask a few more details and supply you with a quote.

To process 400 .xls files, Workbooks, might be better to organise a little VB code, but that would depend on the extent of how difficult it is to determine which rows and/or columns need to be deleted and the amount of re-processing that needs to be done to the remaining data.

To attempt to do what you ask by formula could be achieved by testing (say) the A1 cell for ="" before applying the rest of the formula. Copy - Paste Special, Values will remove the formulas after processing.

The 'a' and 'b' solution (excluding VB code) could be achieved by setting a helper column with =A1&Row() copied down, sorted over, bulk cut/paste the A's to a Workbook and saveAs the remains as the 'B' workbook etc.

Hope this helps, you have some work ahead of you.

Originally Posted by Youyou

i am not sure what specific questions i want to ask but if you can read what
the situation is and help out that will be great.

I have about 400 excel files for my data, in each file, there are some rows
and columns i don't need and some spliting and calulations to do for the rows
and columns that i am interesed in. so~i assume it's a good idea to use MACRO.

say i have file A which contains 200 rows of data, file B which as 210, file
C which has 220 and so on. suppose i worked in file A to setup the macro.
well i got rid of the rows and columns i don't need, slipt the cells. i
assign a function to a cell, drag to apply it through the 200 rows (if i drag
to 300 will create trash data), finish setting up the macro. as everyone can
expect, when apply this macro to file B and file C, there are 10 and 20 rows
left over. so~ is there any way i can make excel to check the end of data and
apply functions accordingly?

also another questions, say i have 5 cells A1 to A5 all contains the data
"a", cell A6 to A10 al have the same data "b", and other columns has other
data. it there something allows be to move the rows start with "a" to a new
spredsheet called "a" and rows start with "b" to a new spredsheet called "b"?
again in these 400 files i have, the number of rows varies, so i will have no
idea how many rows will start with "a'.

just want to process these files in an efficient way.

thanks in advance,

Youyou