Excel file formatting
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. |
Excel file formatting
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 "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 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. |
All times are GMT +1. The time now is 01:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com