Selecting alternate rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large file (Excel XP) with about 300+names and related details. I
need to break it into 2 files but I need to select alternate rows for each
file. It may come out to every 3rd row to work into 3 files.

Is there a quick way to do this rather than tediously select each alternate
row, copy to the new file and delete from the old file?

Many thanks for your help.
Norm.
 
Hi,

You can break your data into two worksheets as follows:

Supposing your data are in Sheet 1 (say A2:A301, B2:B301,......), enter the
following formulas in A2 of Sheet 2 and Sheet 3 respectively..

=OFFSET(Sheet1!$A1,ROW()-1,COLUMN()-1)
=OFFSET(Sheet1!$A1,ROW(),COLUMN()-1)

Drag the formulas across to columns B2, C2, ...... in ech sheet.
Select all the columns in row 2 (i.e., A2, B2, ...), and drag the formula
down the rows to a little more than half the number of rows of the original
sheet (Sheet 1).
You will see zeros in a few bottom rows on sheets 2 and 3 which you can
delete.

Remember that the contents in sheets 2 and 3 still reference sheet 1. To
make them stand-alone, select the entire data-area on sheet 2 (and also sheet
3),
"Copy"/ "Edit"/"Paste Special"/Values.

CAVEAT: Formulas, if any, in Sheet 1 will be lost and only the contents are
transferred to sheets 2 and 3.

Regards,
B. R. Ramachandran
 
Norm,
Assume your data starts in Sheet1!A2. Row 1 is for headers

Now, if your DATA (not headers) in the target sheet starts in row R and
column C, you will use the following formulae:

=OFFSET(Sheet1!$A$2,2*(ROW()-R),COLUMN()-C)
This is for the first target sheet
=OFFSET(Sheet1!$A$2,2*(ROW()-R)+1, COLUMN()-C)
This is for the second target sheet. Uset the necessary numbers for R
and C.

In general, if you want to break it to N sheets, for every N rows,
generalize the two formulas to N formulas, where you substitute N for 2
and, after the (ROW()-R) portion, you add the numbers 0 until N-1.

HTH
Kostis Vezerides
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Printing Alternate Rows 2
How Can I Convert MBOX to HTML File? 4
Excel Help to copy information from one row at a time to another sheet 1
Select each alternate row in a worksheet 1
Deleting blank rows 4
How to delete alternating BLANK rows in .xls file all @ once? 3
reformating data- how to delete alternate blank rows quickly 4
How can I 'CountIf' alternate cells? 2

Back
Top