Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Records; Difficult
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: Befo Column A Column B Column C Column D Column E Column F Column G Ryan 142726 1/6/2009 ChiChi CA 94133 Ryan 142767 1/6/2009 ChiChi CA 94133 Ryan 142792 1/6/2009 ChiChi CA 94133 After: Column A Column B Column C Column D Column E Column F Column G 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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Records; Difficult
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" wrote in message ... 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: Befo Column A Column B Column C Column D Column E Column F Column G Ryan 142726 1/6/2009 ChiChi CA 94133 Ryan 142767 1/6/2009 ChiChi CA 94133 Ryan 142792 1/6/2009 ChiChi CA 94133 After: Column A Column B Column C Column D Column E Column F Column G 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''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Records; Difficult
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: Befo Column A Column B Column C Column D Column E Column F Column G Ryan 142726 1/6/2009 ChiChi CA 94133 Ryan 142767 1/6/2009 ChiChi CA 94133 Ryan 142792 1/6/2009 ChiChi CA 94133 After: Column A Column B Column C Column D Column E Column F Column G 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''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine Records; Difficult
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: Befo Column A Column B Column C Column D Column E Column F Column G Ryan 142726 1/6/2009 ChiChi CA 94133 Ryan 142767 1/6/2009 ChiChi CA 94133 Ryan 142792 1/6/2009 ChiChi CA 94133 After: Column A Column B Column C Column D Column E Column F Column G 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''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine duplicate records in one row - In desperate need!!! | Excel Discussion (Misc queries) | |||
How do I combine multiple records for a single item? | Excel Discussion (Misc queries) | |||
How to Combine Grouped Records onto one row | Excel Programming | |||
Combine several records into one | Excel Discussion (Misc queries) | |||
combine two records | Excel Programming |