Selecting alternate rows

[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.

 

[Guest]

Many thanks - I shall give it a go. If I have a problem, I'll get back to you.
Norm.

 

[Guest]

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

 

[vezerid]

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