Excel merge and formatting

Question

Simply put I am looking for a quick way to do the following.
I would like to format an excel spreadsheet below:

      A     B     C     D
    1 foo   bat   abe   test
    2 foo   bat   cat   test
    3 foo   bat   dog   test
    4 bar   moo   hat   girl
    5 bar   moo   bat   girl

to look like this:

      A     B     C               D
    1 foo   bat   abe, cat, dog   test
    2 bar   moo   hat, bat        girl

Column A, B, D and so on will repeat the same data per unique series in each row.
In each row Column C will always be different.
Column C will need to be merged together comma delimited.

Any suggestions would be welcome.

Answer 1

You could use VBA, but whether that matches your definition of "easy", I'm not sure. Something like:

Range("C1").Select
Do Until Activecell.Formula = ""
   ''if my row matches the row below
   If  ActiveCell.Offset(0,-2).Value = ActiveCell.Offset(1,-2).Value _
   And ActiveCell.Offset(0,-1).Value = ActiveCell.Offset(1,-1).Value _
   And ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value Then
       ''merge and collapse
       Activecell.Formula = ActiveCell.Formula & ", " & _
                            ActiveCell.Offset(1,0).Formula
       ActiveCell.Offset(1,0).EntireRow.Delete
   Else
      ''Go down a row
       ActiveCell.Offset(1,0).Select
   End If
Loop

Goes down through the list one row at a time, and manually concatenates the value if the row appears to match. Otherwise, it just goes down a row.