At each change in data concatenate text values in contiguous ranges
 

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.

At each change in data concatenate text values in contiguousranges
 

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

At each change in data concatenate text values in contiguous ranges
 

Don Guillett Excel MVP wrote on 07/02/2010 08:35 ET :
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

Don, many thanks for this, which works nicely. I haven't written a macro in
VBA
before but with this as a start I should be able to adapt it for variants of
the
problem if I need to. John C