Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many Rows to One Row based off of first column
Thank you so much....
"Greg Wilson" wrote: I just noticed that it looks like when the simple number is the same then the date is the same also and you don't want to repeat the date. This doesn't repeat the date in this case: Sub ConsolidateData2() Dim C As Range, CC As Range Dim ClearRng As Range, DelRng As Range Dim txt1 As String, txt2 As String, txt3 As String Application.ScreenUpdating = False Set C = Range("A3") Set CC = C txt1 = "" txt2 = "" txt3 = "" Set ClearRng = Intersect(C.CurrentRegion, Range("B:C")) Do Until C = "" If C < txt1 Then txt1 = C txt2 = "," & C(1, 2) Do Until CC < txt1 txt3 = txt3 & "," & CC(1, 3) If Intersect(C, CC) Is Nothing Then If DelRng Is Nothing Then Set DelRng = CC Else Set DelRng = Union(DelRng, CC) End If End If Set CC = CC(2) Loop C = C & txt2 & txt3 txt3 = "" End If Set C = C(2) Set CC = C Loop ClearRng.ClearContents DelRng.EntireRow.Delete Application.ScreenUpdating = True End Sub Regards, Greg -----Original Message----- This assumes that the data starts in cell A3 and is in different columns - The simple numbers (199, 200 etc) are in Column A, the dates in Column B and the long numbers in Column C. I understand that you want the data consolidated all into column A. Sub ConsolidateData() Dim C As Range, CC As Range Dim ClearRng As Range, DelRng As Range Dim txt1 As String, txt2 As String Application.ScreenUpdating = False Set C = Range("A3"): Set CC = C txt1 = "" txt2 = "" Set ClearRng = Intersect(C.CurrentRegion, Range("B:C")) Do Until C = "" If C < txt1 Then txt1 = C Do Until CC < txt1 txt2 = txt2 & "," & CC(1, 2) & ", " & CC(1, 3) If Intersect(C, CC) Is Nothing Then If DelRng Is Nothing Then Set DelRng = CC Else Set DelRng = Union(DelRng, CC) End If End If Set CC = CC(2) Loop C = C & txt2 txt2 = "" End If Set C = C(2) Set CC = C Loop ClearRng.ClearContents DelRng.EntireRow.Delete Application.ScreenUpdating = True End Sub Regards, Greg -----Original Message----- I have data that looks like this: 199 7/20/2004 1Z9952220245265501 199 7/20/2004 1Z9952220245959119 199 7/20/2004 1Z9952220244763322 200 7/21/2004 1Z9952220244788457 I need the data to look like this: (commas or spaces) doesn't matter 199,7/20/2004,1Z9952220245265501,1Z9952220245959119,1Z995 2 220244763322 200,7/21/2004,1Z9952220244788457 . . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Many Rows to One Row based off of first column
Just wondering if either Gord or me got what you were
looking for. I see Gord Dibben answered one of your other posts on the subject. We had different interpretations. Being more specific would help get the answer you're lookkng for. Regards, Greg -----Original Message----- Thank you so much.... "Greg Wilson" wrote: I just noticed that it looks like when the simple number is the same then the date is the same also and you don't want to repeat the date. This doesn't repeat the date in this case: Sub ConsolidateData2() Dim C As Range, CC As Range Dim ClearRng As Range, DelRng As Range Dim txt1 As String, txt2 As String, txt3 As String Application.ScreenUpdating = False Set C = Range("A3") Set CC = C txt1 = "" txt2 = "" txt3 = "" Set ClearRng = Intersect(C.CurrentRegion, Range("B:C")) Do Until C = "" If C < txt1 Then txt1 = C txt2 = "," & C(1, 2) Do Until CC < txt1 txt3 = txt3 & "," & CC(1, 3) If Intersect(C, CC) Is Nothing Then If DelRng Is Nothing Then Set DelRng = CC Else Set DelRng = Union(DelRng, CC) End If End If Set CC = CC(2) Loop C = C & txt2 & txt3 txt3 = "" End If Set C = C(2) Set CC = C Loop ClearRng.ClearContents DelRng.EntireRow.Delete Application.ScreenUpdating = True End Sub Regards, Greg -----Original Message----- This assumes that the data starts in cell A3 and is in different columns - The simple numbers (199, 200 etc) are in Column A, the dates in Column B and the long numbers in Column C. I understand that you want the data consolidated all into column A. Sub ConsolidateData() Dim C As Range, CC As Range Dim ClearRng As Range, DelRng As Range Dim txt1 As String, txt2 As String Application.ScreenUpdating = False Set C = Range("A3"): Set CC = C txt1 = "" txt2 = "" Set ClearRng = Intersect(C.CurrentRegion, Range("B:C")) Do Until C = "" If C < txt1 Then txt1 = C Do Until CC < txt1 txt2 = txt2 & "," & CC(1, 2) & ", " & CC (1, 3) If Intersect(C, CC) Is Nothing Then If DelRng Is Nothing Then Set DelRng = CC Else Set DelRng = Union(DelRng, CC) End If End If Set CC = CC(2) Loop C = C & txt2 txt2 = "" End If Set C = C(2) Set CC = C Loop ClearRng.ClearContents DelRng.EntireRow.Delete Application.ScreenUpdating = True End Sub Regards, Greg -----Original Message----- I have data that looks like this: 199 7/20/2004 1Z9952220245265501 199 7/20/2004 1Z9952220245959119 199 7/20/2004 1Z9952220244763322 200 7/21/2004 1Z9952220244788457 I need the data to look like this: (commas or spaces) doesn't matter 199,7/20/2004,1Z9952220245265501,1Z9952220245959119,1Z995 2 220244763322 200,7/21/2004,1Z9952220244788457 . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows based on column values | New Users to Excel | |||
Sum specific column rows based on 2 different column criteria | Excel Worksheet Functions | |||
Adding rows of data based on first column | New Users to Excel | |||
Merging rows based on column value | Excel Discussion (Misc queries) | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) |