Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel crashes on opening exported file; formatting weird afterwards | Excel Discussion (Misc queries) | |||
Is it possible to link cell formatting in Excel from file to file | Excel Discussion (Misc queries) | |||
Formatting in an HTML Excel File | Excel Discussion (Misc queries) | |||
excel formatting to text file | Setting up and Configuration of Excel | |||
Putting Excel formatting and/or formulas into CSV file | Excel Discussion (Misc queries) |