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 Removed) Ryan 142726 1/6/2009 ChiChi CA 94133
(E-Mail Removed) Ryan 142767 1/6/2009 ChiChi CA 94133
(E-Mail 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 Removed) Ryan 142726 | 142767 | 142792 1/6/2009 ChiChi CA
94133

How can it be done?
Thanks,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

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

"ryguy7272" <(E-Mail Removed)> wrote in message
news:ED6DA9A2-1F9F-4662-BE3E-(E-Mail Removed)...
> 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 Removed) Ryan 142726 1/6/2009 ChiChi CA 94133
> (E-Mail Removed) Ryan 142767 1/6/2009 ChiChi CA 94133
> (E-Mail 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 Removed) Ryan 142726 | 142767 | 142792 1/6/2009 ChiChi
> CA
> 94133
>
> How can it be done?
> Thanks,
> Ryan---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

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
---------------
Jacob Skaria


"ryguy7272" wrote:

> 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 Removed) Ryan 142726 1/6/2009 ChiChi CA 94133
> (E-Mail Removed) Ryan 142767 1/6/2009 ChiChi CA 94133
> (E-Mail 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 Removed) Ryan 142726 | 142767 | 142792 1/6/2009 ChiChi CA
> 94133
>
> How can it be done?
> Thanks,
> Ryan---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.

I works perfect Jacob! Thanks for the code and thanks super-fast response.
Ryan---

--

Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "ryguy7272" wrote:
>
> > 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 Removed) Ryan 142726 1/6/2009 ChiChi CA 94133
> > (E-Mail Removed) Ryan 142767 1/6/2009 ChiChi CA 94133
> > (E-Mail 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 Removed) Ryan 142726 | 142767 | 142792 1/6/2009 ChiChi CA
> > 94133
> >
> > How can it be done?
> > Thanks,
> > Ryan---
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.