Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I delete blank rows from excel without selecting them? | Excel Worksheet Functions | |||
Summing Alternate Rows | Excel Discussion (Misc queries) | |||
HOW CAN I AUTOMATICALLY INSERT ALTERNATE BLANK ROWS IN A LIST? | Excel Discussion (Misc queries) | |||
Use of Exact(or other) function for alternate rows? | Excel Worksheet Functions | |||
Identifying exact values in alternate rows | Excel Worksheet Functions |