Selecting alternate rows
 

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.

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

"B. R.Ramachandran" wrote:

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

"Norm75" wrote:

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

"Norm75" wrote:

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.

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