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


.

.

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


.

.


.