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. 
0
Norm75 (12)
10/14/2005 4:13:04 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
491 Views

Similar Articles

[PageSpeed] 54

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. 
0
Norm75 (12)
10/14/2005 4:20:23 AM
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. 
0
10/14/2005 4:21:30 AM
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

0
vezerid (64)
10/14/2005 12:17:49 PM
Reply:

Similar Artilces: