Sort Rows by Mid() string of column cells

NMeeker

New Member
Joined
Feb 10, 2009
Messages
31
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,918
Put =RIGHT(A1,8) in L1 and drag down.
Select A:L and sort on column L.
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,918

ADVERTISEMENT

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

New Member
Joined
Feb 10, 2009
Messages
31
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

New Member
Joined
Feb 10, 2009
Messages
31

ADVERTISEMENT

Okay... been fiddling with it...

Code:
=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

Code:
=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

New Member
Joined
Feb 10, 2009
Messages
31
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
Code:
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

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top