Copy row 1 to new worksheet if column x true

Closed
Report
-
 shraddha_2211988@rediffmail.com -
Hello,

I have a list of manuals on worksheet 1 and I am trying to copy info to new sheet based on request dates.

So, let's say column J is request date.
If a date exists in cell J2, copy row 2 and paste in worksheet 2.
If a date exists in cell J5, copy row 5 and paste in worksheet 2.
... and so forth.

Please help!
Thanks.
MK

4 replies

1. Save the file worksheet in CSV (Comma separated values) to, say, C:/X.csv.

2. Run the following script in biterscripting.

var str data, line, cell ; cat "C:/X.csv" > $data

# If a date exists in cell J2, copy row 2 and paste in worksheet 2. (Cell J is cell 10.)
lex -p -e "2" $data > $line ; wex -p -e "10" $line > $cell
if ( $cell <> "")
    echo $line >> "C:/Y.csv"
endif

# If a date exists in cell J5, copy row 5 and paste in worksheet 2. (Cell J is cell 10.)
lex -p -e "5" $data > $line ; wex -p -e "10" $line > $cell
if ( $cell <> "")
    echo $line >> "C:/Y.csv"
endif

# ... and so forth. 


3. The resulting worksheet is in C:/Y.csv .

Hope this helps.

If you don't have biterscripting, get it free. It is very useful for automating a lot of things. Install it as follows.

1. Get and install biterscripting from http://www.biterscripting.com . (It is free.)

2. Install all sample scripts with the following command.

script "http://www.biterscripting.com/Download/SS_AllSamples.txt"

3. You are ready to go.

Sen Hu
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

One thing I forgot, add the following at the beginning.

cat "" > "C:/Y.csv" # Empty out the output file from last time.
set $wsep = ","


The sys variable $wsep dictates what separates cells. By default, it is set to space, tab, etc. So, currently, it is probably separating cells at spaces, etc.

To automate, put the entire script in a file, say, C:/Z.txt, then call the following

script "C:/Z.txt" 


You can also call biterscripting from any other program (including a web server) with this script as follows.

C:/biterscripting/biterscripting.exe "C:/Z.txt"


Of course, when original CSV file in X.csv is changed, you would have to run the script again to recreate Y.csv.

One thing I did not get, did you only want rows 2 and 5, or did you want to do this on every row ?

Sen
how could i automate copy all of the datas from difference excels to a new excel worksheet to summarize the data?
for example: each class students information is saved in one single excle including the name,age,hometown row.
now i want create a new excel to including all the students information that come from all of the the classes in our schoole by automation, but not by copy and past.

could you help me? i want develop a simple procedure to make it . so i needn't copy the datas from all of the excels to one.

Thanks!
Hen Su,

I'm not getting the result.
Also, is this supposed to be automated? So when a date is entered, it automatically copies that row.


MK

Pl. answer
Sara:

Save all student files in Excel as TSV - Tab Separated Values, as files class1.csv, class2.csv, etc. Then use the following script. ( I will assume all class files are in folder C:\School.)


# Script AllStudents.txt
cd "C:\School"
var str list, file
lf -n "*.csv" "." > $list
echo > "StudentsFromAllClasses.csv"
while ($list <> "")
do
    lex "1" $list > $file
    cat $file >> "StudentsFromAllClasses.csv"
done
system start "StudentsFromAllClasses.csv"



This will open an excel spreadsheet with students in all classes.

To try, copy and paste the above script in file C:/Scripts/AllStudents.txt. Start biterscripting. (You can download it free if you don't already have it on your windows system - just google up the word 'biterscripting'.) Start biterscripting and enter the following command in it.

script "C:/Scripts/AllStudents.txt"

That's all there is to it.

Good luck.