merging cells then deleting rows

[ma...@my-deja.com]

Hi,

The example below shows what I want to do. The values such as "B-2" and
"B-3" are map coordinates (coordinates that are listed on a map to help
you locate a street).

I'm starting with this:

A B
street map coordinates
1 Apple St. B-2
2 Apple St. B-3
3 Cherry St. A-5
4 Peach St. C-1
5 Peach St. C-2

and I want to end up with this:

A B
street map coordinates
1 Apple St. B-2, B-3
2 Cherry St. A-5
3 Peach St. C-1, C-2

Any ideas?

Thanks.

Sent via Deja.com http://www.deja.com/
Before you buy.

[Patrick Molloy]

You can do it in code quite easily

r=1
do until cells(r,1)=""
if cells(r+1,1)=cells(r,1) then
cells(r,2)=cells(r,2) & "," & cells(r+1,2)
rows(r+1).delete
else
r=r+1
end if
loop

this will loop while there's a value in column A

it checks the next row, if the value is the same, then the values in
the second column are joined & the second row deleted
if they're not the same, then the row counter (r) is incremented. Note
that the row counter is not incremented if the next row has been
deleted.
eg if A5 = A6 then B5 = b% & B6 row 6 deleted
now there's a new row 6
otherwise we increase the row and then repeat,
if A6=A7
etc