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
  • The code appears to compare a row's first column and fourth column with the next row. If they match then the next row adopts the values of the first for column 3 and the original row is deleted. It does this for every row between rows 2 and the last row. Assuming "Non Course Person" is your first column and "Sum of Maximum" is your 4th, I see no rows in your example data where the condition would be TRUE. From what I can tell your code works fine. – JNevill Jul 15 '14 at 15:57
  • Thanks for the feedback. Not sure why it's not doing anything then when I try to run it then. Am I missing a step when inserting the code?
    1. Open VBA window
    2. Insert Module
    3. Paste code
    4. Run/F5

    ...and nothing happens.

    Also I triple checked the security settings and macros are enabled and all that jazz.

     – Gretchen Jul 15 '14 at 16:07
  • I really think it might be the way your data is set up. If the purpose of the code is to dedup your records, then before running the code try sorting your records by column A and column D, so duplicates are sitting next to each other. The code should then go record by record, comparing it to the very next record and removing any duplicates that are found. Your process for entering and running the code is sound. – JNevill Jul 15 '14 at 16:22
  • Not necessarily remove duplicates, but condense- if that makes sense. Ideally I need for each ID(column A) to have all three scores on one line (ESS, WRT & REA).

    I did a custom sort prioritizing Column A (ID #) then Column C then Column D. And tried to run it again, but no luck.

    Essentially only the first two columns (A&B) contain duplicates. If it provides any context, these are test scores for 3 sections attached to ID numbers. I'm trying to get each person's results on a single line rather than each person's partial results being on a single line.

     – Gretchen Jul 15 '14 at 16:53

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,562
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,232
  • Thank you for all of your work. I tested it out, but the data seems to be overwriting incorrectly. The final three rows (11,12 & 13 are the same personid).

    Before http://i.imgur.com/1VEkKh1.jpg

    After http://i.imgur.com/0KSMSka.jpg

     – Gretchen Jul 17 '14 at 16:55
  • I just updated the VBA to better fit your scenario. It now compares each row to the next row. If a match is found, then it copies values down to the next row for each course score and deletes the current row. I believe it should work now... or be very close to working. – JNevill Jul 18 '14 at 20:14