merging cells and then deleting rows and duplicate entry

[ndav79]

Hi,
the example below shows what i want to do.

first last address city zip language
john d 125 55 st ny 11111 spanish
john d 125 55 st ny 11111 english
john d 255 24 st ny 12444 spanish
john d 255 24 st ny 12444 english

and so on.... with different names and a person having maybe four
address or knows 3 languages

I want it to end up like this:

first last address city zip language
john d 1.125 55 st ny 1.11111 spanish,english
2.255 24 st 2.12444
in one row. The excel file is pretty big with many names.

I found this code but it needs more so the same address for example
would not show twice in a cell.

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

Any thoughts would be apreciated.
thanx

 

[Patrick Molloy]

assume your table is A-F, add the headings are in row 1
also assume the data is sorted
add two more columns

key
=A2 & "_" & B2 & "_" & E2
languages
=IF(G2=G3,H3 & "," &F2,F2)

the idea is to generate a unique key - the example uses firat & last name &
zip
the the laguages formula adds the language if the next rows key is identical.
All you need to do is
(1) for the languages column, make the formula into values
VBA:
Range("H:H").Value =Range("H:H").Value

(2) loop to remove the unrequired rows
VBA:
Sub FinishItOff()
Dim rw As Long
For rw = Range("G1").End(xlDown).Row To 3 Step -1
If Cells(rw, "G") = Cells(rw - 1, "G") Then
Rows(rw).Delete
End If
Next
End Sub

 

[palikari]

Thanks for the feedback, but I'm kind of lost. Should I include the
following procedure?
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

and also where should i put the following formula?
Range("H:H").Value =Range("H:H").Value

I think its getting somewhere but i feel sth is missing.

Thanks again

 

[palikari]

Any thoughts pleaseeeeeee?

thanx