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
.
.
|