Excel - Copy row 1 to new worksheet on condition

December 2016

Copying rows from one sheet to another can be done in Excel. The worksheet should be saved to contain values in the CSV or Common Separated Values format. Biterscripting can be used to copy a row to a new worksheet if a particular condition is true. Biterscripting is a scripting tool that can be used to write small and efficient programs to automate repetitive tasks. It's available for free and can be downloaded from the internet. Biterscripting is particularly useful for writing scripts to automate copying of rows from an Excel Office Software worksheet to a new worksheet.



Issue


I have a list of manuals on worksheet 1 and I am trying to copy info to a 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.

Solution

  • Save the file worksheet in CSV (Comma separated values) to, say, C:/X.csv.
  • 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.
  • 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:

script "http://www.biterscripting.com/Download/SS_AllSamples.txt"
  • You are ready to go.

Note

I forgot one thing, 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 understand, did you only want rows 2 and 5, or did you want to do this on every row?

Thanks to SenHu for this tip on the forum.

Related :

This document entitled « Excel - Copy row 1 to new worksheet on condition » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.