Excel file formatting

[Jcraig713]

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 looks
like this:

Name Grade Period Room
Doe, John 12th 1 101
Doe, John 12th 2 102
Doe, John 12th 3 103 etc.....

This data is exported from our database like that through period 7. What I
would like for mail merge purposes is to get each student (if the same
perhaps on student #) on one row (record) so the mail merge knows where to
put the info. What I would like to do is 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 10 110 111 112 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.

 

[Joel]

Try this code. Change Sheet1 to the name of where your student data is
located.


Sub MergeStudents()

Set MergeSht = Sheets.Add(Sheets(Sheets.Count))
With MergeSht
.Name = "Merge Students"
.Range("A1") = "Name"
.Range("B1") = "Grade"
For i = 1 To 7
.Cells(1, i + 2) = "Per" & i & "Rm"
Next i
End With

NewRowCount = 2
With Sheets("Sheet1")
StudentName = .Range("A2")
MergeSht.Range("A" & NewRowCount) = StudentName
StudentGrade = .Range("B2")
MergeSht.Range("B" & NewRowCount) = StudentGrade
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
If .Range("A" & RowCount) <> StudentName Then
NewRowCount = NewRowCount + 1
StudentName = .Range("A" & RowCount)
MergeSht.Range("A" & NewRowCount) = StudentName
StudentGrade = .Range("B" & RowCount)
MergeSht.Range("B" & NewRowCount) = StudentGrade
End If
Period = .Range("C" & RowCount)
Room = .Range("D" & RowCount)
MergeSht.Cells(NewRowCount, Period + 2) = Room
Next RowCount
End With


End Sub