On Jul 2, 2:28*am, john-c wrote:
I am editing a book for publication and I want to use Excel as an aid to
*compiling the index. I need a way of concatenating the page numbers, which
will
*be stored as text values in contiguous ranges of cells that are defined by
*changes in a text variable. The concatenated values also need to be separated
by
*commas.

*Thus, if this is the input:

*row0 * Col A * * * * * Col B
*row1 * * antelope * * * * * * *21
*row2 * * antelope * * * * * * *22-3
*row3 * * antelope * * * * * * *25
*row4 * * bears * * * * 19
*row5 * * bison * * * * 31
*row6 * * bison * * * * 33
*row7 * * Colorado * * * * * * *14
*row8 * * Colorado * * * * * * *17

*the desired output would be:

*row0 * * Col X * * * * Col Y
*row1 * * antelopes * * * * * * 21, 22-3, 25
*row2 * * bears * * * * 19
*row3 * * bison * * * * 31,33
*row4 * * Colorado * * * * * * *14, 17

*(Alternatively the output could be on rows 1, 4, 5, 7 etc; or on rows 3, 4, 6,
8
*etc; or anywhere else convenient).

*This is similar to the query titled "At each change in data apply
*formula", posted by "Shon" on December 11th, 2009, but I'm not
*sure that it is quite the same. I would appreciate any suggestions for how to
*approach this.

The macro below will do as desired

Option Explicit
Sub rearrangedataSAS()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) = Cells(i, 1) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & "," &
Application.Trim(Cells(i, 2))
Rows(i).Delete
End If
Next i
End Sub