Combine Records; Difficult

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

Hello everyone! I have data in Columns A to G. I have a list of names in
Column B. If the names are arranged in descending order (so similar names
appear in above or below cells). If two names are the same, I'd like to take
the data in Column C, and combine the records, separated just by the pipe
character '|'. If the names in Column B are the same, all data in Column A,
as well as d to G will be the same. Only differences are in Column C. For
instance:
Before:
Column A Column B Column C Column D Column E Column F Column G
(e-mail address removed) Ryan 142726 1/6/2009 ChiChi CA 94133
(e-mail address removed) Ryan 142767 1/6/2009 ChiChi CA 94133
(e-mail address removed) Ryan 142792 1/6/2009 ChiChi CA 94133

After:
Column A Column B Column C Column D Column E Column F Column G
(e-mail address removed) Ryan 142726 | 142767 | 142792 1/6/2009 ChiChi CA
94133

How can it be done?
Thanks,
Ryan---
 
for rw = range("C1").End(xldown).Row to 2 step -1
if cells(rw,"B").Value = Cells(rw-1,"B").Value then
Cells(rw-1,"C").Value = Cells(rw-1,"C").Value & " | " &
Cells(rw,"C").Value
rows(rw).Delete
end ifnext
 
Ryan, try this..

Sub Macro()
Dim lngRow As Long
For lngRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If StrComp(Range("B" & lngRow), Range("B" & lngRow - 1), vbTextCompare) = 0
Then
If Range("C" & lngRow) <> "" Then
Range("C" & lngRow - 1) = Range("C" & lngRow - 1) & "|" & Range("C" & lngRow)
End If
Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
 
Back
Top