Sort Rows by Mid() string of column cells

[NMeeker]

I have a single column dynamic range called "Dock" and I need to sort the rows in my worksheet based on information from this range.

Each cell in "Dock" is formated similarly to "TM09030010"
This is "TM" & YYMM & "4 digit number"

I need to sort my rows based first on Date "YYMM" then second by the last 4 digits of the same string.

The worksheet has information in columns A through K and Range "Dock" is located within column A.

Any help would be appreciated

 

[mikerickson]

Put =RIGHT(A1,8) in L1 and drag down.
Select A:L and sort on column L.

 

[ajetrumpet]

i don't think you can do this with the Sort option from the tools menu, but you might want to research the SORT method in visual basic. there should be an example in there of the syntax. You may be able to use the LEFT() and MID() functions to extract the sort data that you want in the entire range, or even in the column itself. It is worth trying I would say. I have not tested it.

 

[ajetrumpet]

Put =RIGHT(A1,8) in L1 and drag down.
Select A:L and sort on column L.

I make things so complicated. lol

 

[mikerickson]

You're not the only one, I just realized that if every entry in col A starts with "TM" there is no need for column L.

Sorting A:K on column A will sort as NMeeker requested.

 

[NMeeker]

You're not the only one, I just realized that if every entry in col A starts with "TM" there is no need for column L.

Sorting A:K on column A will sort as NMeeker requested.

MMM not quiet some of the dates YYMM go back below 1995... therefore between 99' and 00' simple sorting went out the window.
And may not always be "TM"

I suppose I should have clarified these things... wasn't thinking about it.

Originally Posted by mikerickson
Put =RIGHT(A1,8) in L1 and drag down.
Select A:L and sort on column L.

Sorry, This one isn't gonna work either trying to avoid leaving formulas in extra column, thought briefly about this one... The only way I could agree with that one is if this column were inserted, sorted, then removed all within the same macro.... I'm not sure how to do that one though...

Also.. should have mentioned, Trying to do this in VBA
sorry for the extra trouble, but I do appreciate your help

 

[NMeeker]

Okay... been fiddling with it...

=DATE(IF(MID(A2,3,1) = "9", MID(A2,3,2),20 & MID(A2,3,2)),(MID(A2,5,2)),1)

Will give me a Date from the YYDD section

=Right(A2,4)

Will give me the last for numbers...


So sorting on column L as a date then column M A-Z will do the sort right...
Does anyone have any idea how to set this up to work in VBA as a sub?
I only want to sort data on Rows that are part of Range("Dock") And I would prefer not leaving formulas in extra columns on the worksheet.

Any suggestions?

 

[NMeeker]

Bump... not solved yet....

 

[NMeeker]

Go Figure... I've done a lot of trial and error to get me to this point... but I need a little more help.. if you can...

Code So Far

Sub DockSort(SheetName As String)
Dim Cel As Range
For Each Cel In ThisWorkbook.Worksheets(SheetName).Range("Dock")
    If Left(Cel.Value, 2) Like "TM" Then
        Cel.Offset(0, 11).Value = "=DATE(IF(MID(A" & Cel.row & ",3,1) = ""9"", MID(A" & Cel.row _
        & ",3,2),20 & MID(A" & Cel.row & ",3,2)),(MID(A" & Cel.row & ",5,2)),1)"
        Cel.Offset(0, 12).Value = "=MID(A" & Cel.row & ",7,4)"
    End If
Next

'Need Selection.sort on Column L as a date then Column M as Number
'BUT!!! This Needs to ONLY sort the information within the rows referenced by Range("Dock")

'Next I would need to Delete Information in Columns L And M

End Sub

Like I said within the code, I still need to delete columns L and M when I am done, And i still need to figure out how to sort Column L by a date...
then how to sort column M by number "Even though the numbers are referenced as strings"

Any Help Would be Appreciated!!!!!

 

[PCL]

Why not to add an extra column to prepare the filter tehn remove this column at the end.
How do you make the distinction between 1900 and 2000 dates, is only 9 after TM the reason.