merging cells then deleting rows

420 views
Skip to first unread message

ma...@my-deja.com

unread,
Dec 6, 1999, 3:00:00 AM12/6/99
to
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

unread,
Dec 6, 1999, 3:00:00 AM12/6/99
to
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

Reply all
Reply to author
Forward
Message has been deleted
0 new messages