PDA

View Full Version : Sort using part of cell value



KongUK
10-13-2017, 03:58 AM
Hi

I need to parse Right function to sort order key ie

Cell values per row are PART-***X, need to sort on last 5 characters (First part of value is not just PART)
Right(PART-1234, 5) = -1234

Can I fit the Right function into the sort key when using this approach instead using an additional calculated column?


ActiveSheet.Sort.SortFields.Add Key:=Range("E9:E" & lastrow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlTextAsNumbers
With ActiveSheet.Sort
.SetRange (MyRange)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Thanks

mikerickson
10-13-2017, 07:47 AM
Something like this should work. Using built in Excel features would require a helper column, this does not.


Sub test()
Dim lastRow As Long
Dim arrData As Variant
Dim i As Long, j As Long, temp As Variant

lastRow = 10: Rem adjust

With Range("E9:E" & lastRow)
arrData = .Value
For i = 1 To lastRow - 10
For j = i + 1 To lastRow - 9
If LT(arrData(j, 1), arrData(i, 1)) Then
temp = arrData(i, 1)
arrData(i, 1) = arrData(j, 1)
arrData(j, 1) = temp
End If
Next j
Next i
.Value = arrData
End With
End Sub

Function LT(a As String, b As String) As Boolean
LT = (Right(a, 5) < Right(b, 5))
End FunctionThis could be modified to use a Quicksort and it could also be adjusted if you have more than one column for the sort.

KongUK
10-13-2017, 08:04 AM
Thank you mikerickson, I will try it when back at work on Monday :-)