Combining data from multiple rows into one row

[jenabeyta1]

I need to combine data for into one row from multiple that contain duplicate data as well. My example data is as follows:


ID Last Name First Name Grade Course Teacher

111 Smith Bob D Biology Mr. Walters

111 Smith Bob F Algebra Ms. Sunny

129 Garcia Jose F Civics Mr. Ford

129 Garcia Jose D English I Mr. Torres

259 Kipper Jane F Civics Ms. Justice

583 Lopez Reyna D Geometry Ms. Fuentes

583 Lopez Reyna D Choir Ms. Carson

I need it to look like...


ID Last Name First Name Grade Course Teacher Grade Course Teacher

111 Smith Bob D Biology Mr. Walters F Algebra Ms. Sunny

129 Garcia Jose F Civics Mr. Ford D English I Mr. Torres

259 Kipper Jane F Civics Ms. Justice

583 Lopez Reyna D Geometry Ms. Fuentes D Choir Ms. Carson


Can anyone help? If there is a video as well that would be great!

 

[Luke M]

You could use this macro to do it. Right click on the sheet tab and goto "View Code". Paste this in, then run the macro.

[pre]

Sub ShiftData()
Dim ColCounter As Integer
Dim ID As String
Dim IDRow As Integer
Dim StartingCol As Integer

Application.ScreenUpdating = False

StartingCol = 7 'New data goes in col G
ColCounter = StartingCol
IDRow = 2
i = 2
Do
If Cells(i, "A") = ID Then
Range(Cells(i, "D"), Cells(i, "F")).Copy Range(Cells(IDRow, ColCounter), Cells(IDRow, ColCounter + 2))
ColCounter = ColCounter + 3
Rows(i).Delete
Else
ID = Cells(i, "A").Value
IDRow = i
ColCounter = StartingCol
i = i + 1
End If
Loop While Cells(i, "A").Value <> ""
Application.ScreenUpdating = True
End Sub

[/pre]