At each change in data concatenate text values in contiguous ranges

[john-c]

I am editing a book for publication and I want to use Excel as an aid t
compiling the index. I need a way of concatenating the page numbers, whic
wil
be stored as text values in contiguous ranges of cells that are defined b
changes in a text variable. The concatenated values also need to be separate
b
commas

Thus, if this is the input

row0 Col A Col
row1 antelope 2
row2 antelope 22-
row3 antelope 2
row4 bears 1
row5 bison 3
row6 bison 3
row7 Colorado 1
row8 Colorado 1

the desired output would be

row0 Col X Col
row1 antelopes 21, 22-3, 2
row2 bears 1
row3 bison 31,3
row4 Colorado 14, 1

(Alternatively the output could be on rows 1, 4, 5, 7 etc; or on rows 3, 4, 6

etc; or anywhere else convenient)

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

 

[Don Guillett Excel MVP]

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 forhow 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

 

[john-c]

Don Guillett Excel MVP wrote on 07/02/2010 08:35 ET

On Jul 2, 2:28 am, john-c wrote
The macro below will do as desire

Option Explici
Sub rearrangedataSAS(
Dim i As Lon
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -
If Cells(i - 1, 1) = Cells(i, 1) The
Cells(i - 1, 2) = Cells(i - 1, 2) & "," &amp
Application.Trim(Cells(i, 2)
Rows(i).Delet
End I
Next
End Su

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