Copy Down With Multiple Values

[Guest]

I have a spreadsheet that I didn't design with around 4000+ Rows containing
student information.

The !@#$ who designed the sheet organized it like below:
Columns
A: Teacher Name (Only in the Row above the student names)
B: Student Name (In every row except the first)
C: Student ID (In every row except the first)
D: Period (Only in the Row above the student names)

I want the name of the teacher and the period in every row, like an access
table, so I can sort the stupid thing.

I don't want to have to re-type 4000 rows of data.

Is there a way that I can use the copy down function or a macro to recognize
when there is a new teacher/period and change the information as it copies
down the worksheet? Any help is much appreciated.

 

[Peo Sjoblom]

Stupid setup I agree, however it can be easily fixed if I understand you
correctly. Try this on a small sample first

Select the range in column A, press F5, select special and blanks, click OK
Type an equal sign and press up arrow once, press Ctrl + Enter
Select the whole range in column A again, press Ctrl + C, do edit>paste
special and select values

Voila!

 

[JE McGimpsey]

One way:

Select the column with Teacher names. Choose Edit/Goto/Special/Blanks.
In the first blank cell, say, A3, type

=A2

where the reference should be to the cell above the active cell. Hit
Ctrl-Enter to insert the formula in each blank cell.

Select the column again. Copy it. Choose Edit/Paste Special, selecting
the Values radio button.

 

[Guest]

YOU GUYS ROCK! That saved me HOURS of work.