Excel - Copy row 1 to new worksheet on condition

January 2017

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


Published by jak58. Latest update on November 6, 2016 at 02:11 PM by owilson.
This document, titled "Excel - Copy row 1 to new worksheet on condition," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).