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.

--
Thanks As Always
Rip

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 editpaste
special and select values

Voila!


--
Regards,

Peo Sjoblom



"Ripper" wrote in message
...
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.

--
Thanks As Always
Rip

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.



In article ,
Ripper wrote:

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.

YOU GUYS ROCK! That saved me HOURS of work.

--
Thanks As Always
Rip


"Ripper" wrote:

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.

--
Thanks As Always
Rip