Copy and Pest in Excel

  • Thread starter Thread starter wasi64
  • Start date Start date
W

wasi64

I am working with an Excel worksheet. The worksheet contain students
schedule for a high school. Most of the students are having two rows.
The first row contains student and their class name. The second rows
containing the room number bellow the class name. Some students are
occupying 4 rows. First two rows are same for every one but other two
rows containing additional class at the same period. Example: at the
4th period some students have 'GYM' for 3 days and 'LAB' for 2 days.
This ‘LAB’ is occupying extra 2 rows.

Now I like cut this 'LAB' class and pest that to the same rows that
contains the name and room number of the student.

How can I do that? I have +/- 4000 students.
 
The following procedure requires that you have no header rows. You should make a copy of your sheet and delete ant header rows. Then after you have resorted the dat you can delete the original list and paste in the new list

This also assumes the data is in columns A and B before you start

1)Insert 2 columns in from of column

2)enter 1 in cells A1 and B

3) enter 2 in cell A2. Select A1 and A2, and drag fill handle to bottom of sheet. The fill handle is the little black sqaure in the lower right hand corner of the selected cells

4) enter the following formula in B2 and Fill Down to bottom of sheet
=IF(C2<>"",1,B1+1

5) enter the following formula in E1
=IF(C3="",D3,""

6) enter the following formula in E2
=IF(C3="",D4,""

7) select cells E1 and E2. drag fill handle to bottom of sheet

8) Select the entire sheet, by clicking on the gray square above row 1 and to the left of column

9) Edit>Copy and then Edit>Paste_Special>Values

10) Sort on Column B and delete all rows with 3 and 4 in column B

11) Sort on Column A to return list to original sequence

12) Delete Columns A and B

If you have any questions just post on this thread

Good Luck
Mark Graesse
(e-mail address removed)

----- wasi64 wrote: ----

I am working with an Excel worksheet. The worksheet contain student
schedule for a high school. Most of the students are having two rows
The first row contains student and their class name. The second row
containing the room number bellow the class name. Some students ar
occupying 4 rows. First two rows are same for every one but other tw
rows containing additional class at the same period. Example: at th
4th period some students have 'GYM' for 3 days and 'LAB' for 2 days
This ‘LAB’ is occupying extra 2 rows

Now I like cut this 'LAB' class and pest that to the same rows tha
contains the name and room number of the student

How can I do that? I have +/- 4000 students
 
Back
Top