This uses the student id as the key value. (I think that there's less chance of
duplication compared to using the name).

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim res As Variant

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

'sort original range by Id, name, period
With .Range("a1:E" & LastRow)
.Sort key1:=.Columns(5), order1:=xlAscending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(3), order3:=xlAscending, _
header:=xlYes
End With

'Get a list of unique class periods
.Range("C1:C" & LastRow).AdvancedFilter _
action:=xlFilterCopy, unique:=True, copytorange:=NewWks.Range("A1")
End With

With NewWks
With .Range("a:a")
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With

.Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Copy
.Range("D1").PasteSpecial Transpose:=True
.Range("a:c").Clear
End With

With CurWks
oRow = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, "E").Value < .Cells(iRow - 1, "E").Value Then
'different student
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "E").Value
Else
'same person as before.
'don't add "headers"
End If
res = Application.Match(.Cells(iRow, "C").Value, NewWks.Rows(1), 0)
If IsError(res) Then
'shouldn't happen!
MsgBox "Error with row: " & iRow
Else
NewWks.Cells(oRow, res).Value = .Cells(iRow, "d").Value
End If
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub


Jcraig713 wrote:

Hello. I have data in my excel file which contains many many rows; 9900 to
be exact, of student names and room locations in a building. The file (raw
data) looks like this in excel:

ColA Col B Col C Col D Col E
Name Grade Period Room Student ID#
Doe, John 12th 1 101 199999999
Doe, John 12th 2 102 199999999
Doe, John 12th 3 103 199999999
Doe, John 12th 4 104 199999999
Doe, John 12th 5 105 199999999
Doe, John 12th 6 106 199999999
Doe, Jane 11th 1 201 299999999
Doe, Jane 11th 2 202 299999999

etc........

This data is exported from our software system database like that through
period 7. What I would like for mail merge purposes or better yet, in excel
to create a report, is to get each student as listed in multiple rows, on one
row (record) so the mail merge knows where to put the info. What I would
like to do create programming that reads the raw data and reformat the file
to look like:

Name Grade Per 1 Rm Per 2 RM Per 3 RM
Doe, John 12 101 102 103
Doe, Jane 11 201 202 203 etc....

Is there a way to do this reformatting in excel without having to do this
one record at a time? Your assistance would be greatly appreciated.

--

Dave Peterson