C
carl
I am using this code:
______________________________________________________-
Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value
Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
___________________________________________________
This worked great until we started using upper and lower case
characters in one of the data fields. For example, my data table now
looks like this:
Code Number
HO 549
HO 910
HO 919
HO 980
HO 918
HO 937
HO 051
ho 549
ho 910
HO 642
When I run the code above, it produces this result:
HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980
I am looking for this result:
HO 051;549;642;910;918;919;937;980
ho 549;910
Is there a way to modify the code to produce the desired result ?
If not, is there code that can produce the desired result ?
Thank you in advance.
______________________________________________________-
Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value
Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
___________________________________________________
This worked great until we started using upper and lower case
characters in one of the data fields. For example, my data table now
looks like this:
Code Number
HO 549
HO 910
HO 919
HO 980
HO 918
HO 937
HO 051
ho 549
ho 910
HO 642
When I run the code above, it produces this result:
HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980
I am looking for this result:
HO 051;549;642;910;918;919;937;980
ho 549;910
Is there a way to modify the code to produce the desired result ?
If not, is there code that can produce the desired result ?
Thank you in advance.