0

So I researched this for awhile, but I seem to be hitting a wall. I will admit- I successfully did this once, made a tutorial for my coworkers- then I nor anyone else was able to replicate the results. I'm mildly competent with excel- but VBA and coding in general I'm still rather new at (so my apologies if I'm missing something obvious).

I used the VBA code from this question, How to combine values from multiple rows into a single row in Excel?

Sub CombineRowsRevisited()

Dim c As Range
Dim i As Integer

For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then
        c.Offset(,3) = c.Offset(1,3)
        c.Offset(1).EntireRow.Delete
End If

Next

End Sub

That worked successfully once. I tried it again, an hour later and when I run the same code- nothing happens. I press the run button and it all looks the same.

I got desperate and tried the other code in the previously referenced question, but it wouldn't work with the way my data is setup/ the original author preferred the initial formula anyway.

Below is an example of the data I'm working with. Anyone have any suggestions or identify any obvious errors on my part?

enter image description here

Also, I'm not concerned with the data in the NON COURSE ID being overwritten.

Community
  • 1
Gretchen
  • 1

2 Answers2

0

I would use the Power Query Add-In to meet this requirement. A Query can start from an existing Excel table. I would use the Group By command to define the "combining" logic you need.

http://office.microsoft.com/en-au/excel-help/group-rows-in-a-table-HA103993875.aspx?CTT=5&origin=HA103993930

I would deliver the result to an Excel Table.

Mike Honey
  • 2,632
0

I believe this will do what you are looking for. It's nearly the same as the original, but I added in some fluff to make it more understandable and easier to modify. 

Sub CombineRowsRevisited()

    Dim c As Range
    Dim i As Integer
    Dim PersonID, REAScore, WRTScore, startDate, courseID as integer

    'Columns as I understand them (subtracting one from the actual column in the sheet)
    PersonID = 0    'personid is in column 1  etc..
    '...
    startDate = 1
    courseID = 2
    REAScore = 3    
    WRTScore = 4
    ESSScore = 5


    'Looping through each record
    For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))

      'If personID on this record is the same as the personID on the next record
      If c.Offset(0, PersonID ) = c.Offset(1, PersonID ) Then

            'blindly overwrite startDate and courseID with the value from the next row
            c.offset(1, startDate ) = c.Offset(0, startDate)
            c.offset(1, courseID) = c.offset(0, courseID)

            'only copy the scores if they are not null
            if c.offset(0, REAScore).value <> vbNull then c.offset(1, REAScore) = c.offset(0, REAScore)
            if c.offset(0, WRTScore).value <> vbNull then c.offset(1, WRTScore) = c.offset(0, WRTScore)
            if c.offset(0, ESSScore).value <> vbNull then c.offset(1, ESSScore) = c.offset(0, ESSScore)

            'Just added this to delete the next row when a match is found
            c.entireRow.Delete
      End If
    Next

End Sub
JNevill
  • 1,241
  • 6
  • 12