Sorting two columns

M

Mabocat

Board Regular
Joined
Apr 20, 2011
Messages
74
I have two columns of times, as below which are derived from formulaes from other sheets


<tbody>
[TD="class: xl66"]Time[/TD]
[TD="class: xl81, width: 67"]Out[/TD]
[TD="class: xl67, width: 77"]T/O[/TD]

[TD="class: xl76"]1:00[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl73"]LG2[/TD]

[TD="class: xl80"]3:20[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl78"]LG1[/TD]

[TD="class: xl68"]3:30[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl73"]LG1[/TD]

[TD="class: xl76"][/TD]
[TD="class: xl79"]3:30[/TD]
[TD="class: xl78"]LG2[/TD]

[TD="class: xl76"]5:22[/TD]
[TD="class: xl75"]5:31[/TD]
[TD="class: xl73"]LP8[/TD]

[TD="class: xl68"]5:55[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl70"]SF2[/TD]

[TD="class: xl76"]6:42[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl73"]SE1[/TD]

[TD="class: xl76"]7:50[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl73"]LP11[/TD]

[TD="class: xl68"]8:42[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl70"]G80[/TD]

</tbody>


but when I alter some times on the other sheets, they change on this time list & what I am trying to do is a macro that resorts them so the Out column is still in the correct relationship with the time column.

<tbody>
[TD="class: xl66"]Time[/TD]
[TD="class: xl81, width: 67"]Out[/TD]
[TD="class: xl67, width: 77"]T/O[/TD]

[TD="class: xl76"]3:57[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl73"]LG2[/TD]

[TD="class: xl80"]3:20[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl78"]LG1[/TD]

[TD="class: xl68"]6:27[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl73"]LG1[/TD]

[TD="class: xl76"][/TD]
[TD="class: xl79"]6:27[/TD]
[TD="class: xl78"]LG2[/TD]

[TD="class: xl76"]5:22[/TD]
[TD="class: xl75"]5:31[/TD]
[TD="class: xl73"]LP8[/TD]

[TD="class: xl68"]5:55[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl70"]SF2[/TD]

[TD="class: xl76"]6:42[/TD]
[TD="class: xl77"][/TD]
[TD="class: xl73"]SE1[/TD]

[TD="class: xl76"]7:50[/TD]
[TD="class: xl75"][/TD]
[TD="class: xl73"]LP11[/TD]

[TD="class: xl68"]8:42[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl70"]G80[/TD]

</tbody>

so it ends up like this
[TABLE="width: 211"]
<tbody>[TR]
[TD]Time[/TD]
[TD]Out[/TD]
[TD]T/O[/TD]
[/TR]
[TR]
[TD]3:20[/TD]
[TD][/TD]
[TD]LG1[/TD]
[/TR]
[TR]
[TD]3:57[/TD]
[TD][/TD]
[TD]LG2[/TD]
[/TR]
[TR]
[TD]5:22[/TD]
[TD]5:31[/TD]
[TD]LP8[/TD]
[/TR]
[TR]
[TD]5:55[/TD]
[TD][/TD]
[TD]SF2[/TD]
[/TR]
[TR]
[TD]6:27[/TD]
[TD][/TD]
[TD]LG1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6:27[/TD]
[TD]LG2[/TD]
[/TR]
[TR]
[TD]6:42[/TD]
[TD][/TD]
[TD]SE1[/TD]
[/TR]
[TR]
[TD]7:50[/TD]
[TD][/TD]
[TD]LP11[/TD]
[/TR]
</tbody>[/TABLE]



How do I do this please ? Currently I manually shift the rows.
Ron
 
Sort by date Sort by votes
Thanks Peter, I will have a play.
So column 4 is used for what? The macro copies data from both columns A & B to D ? then sorts the range A-C using D ? how does the sort work then when I have data in both A & B of the same row?


I have overcome the problem in that the blank cell has the same data as the Out cell but with white font so it doesn't show up thus sorting on the Time column works OK

Ron
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Mabocat said:
I have overcome the problem in that the blank cell has the same data as the Out cell but with white font so it doesn't show up thus sorting on the Time column works OK
Click to expand...
In that case, there is no need for the intermediate steps that I had my code doing. As you have found, & AlphaFrog suggested earlier, a simple sort will do it - & the macro is very simple.
Code: 
Sub Reorder_v2()
  Range("A2", Range("C" & Rows.Count).End(xlUp)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
End Sub
 
Upvote 0
I am slightly lost - this is part of what I recorded
Application.Goto Reference:="Watchit_data"
ActiveWorkbook.Worksheets("Stations").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stations").Sort.SortFields.Add Key:=Range( _
"B49:B59"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

the time column is B and the sort field is based on data B49:B59 and I tried to changed it to read as "Watchit_time" but it failed
Now it is quite possible that the actual overall data range for Watchit could change due to additional rows further up the spreadsheet so the Time data to be sorted is now B61:B71 and it still works OK - how come?
 
Upvote 0
Hard to advise when we don't know exactly what you have, where, nor exactly what "Watchit_data" and "Watchit_time" represent.
 
Upvote 0
Watchit_data is the entire range of 6 columns made up text & numbers( time)
Watchit_time is column B which has the time

[TABLE="width: 577"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"] WATCHIT[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Refer to TO for details[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Time[/TD]
[TD]Out[/TD]
[TD]T/O[/TD]
[TD] [/TD]
[TD]OP1[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]1:00[/TD]
[TD] [/TD]
[TD]LG2[/TD]
[TD] Prepare for LG2 Local goods - deps 3:30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]3:20[/TD]
[TD] [/TD]
[TD]LG1[/TD]
[TD]Local Goods[/TD]
[TD]Driven in by Marabost[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]3:30[/TD]
[TD]3:30[/TD]
[TD]LG2[/TD]
[TD]Local Goods[/TD]
[TD]Driven out by Marabost[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]3:30[/TD]
[TD] [/TD]
[TD]LG1[/TD]
[TD]Local Goods[/TD]
[TD]Shunting[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]5:22[/TD]
[TD]5:31[/TD]
[TD]LP8[/TD]
[TD]DMU2[/TD]
[TD]Departs West Watchit[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]5:55[/TD]
[TD] [/TD]
[TD]SF2[/TD]
[TD]Stone[/TD]
[TD] Make up Train - deps 6:15[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]6:42[/TD]
[TD] [/TD]
[TD]SE1[/TD]
[TD]Stone[/TD]
[TD]Departs Newton Abbot[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]7:50[/TD]
[TD] [/TD]
[TD]LP11[/TD]
[TD]DMU2[/TD]
[TD]*** departs Tawnton[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]8:42[/TD]
[TD] [/TD]
[TD]G80[/TD]
[TD]Goods[/TD]
[TD]Departs Bruton[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]9:32[/TD]
[TD]9:41[/TD]
[TD]LP16[/TD]
[TD]DMU2[/TD]
[TD]Departs West Watchit[/TD]
[/TR]
[TR]
[TD][ ][/TD]
[TD]10:40[/TD]
[TD] [/TD]
[TD]LP17[/TD]
[TD]DMU2[/TD]
[TD]*** departs Tawnton[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Mabocat said:
Watchit_data is the entire range of 6 columns made up text & numbers( time)
Watchit_time is column B which has the time
Click to expand...
That is still not entirely clear to me.
I have assumed that
- "Watchit_data" includes the heading row with "Time", "Out" etc. So A48:F59 for my layout below.
- "Watchit_time" does not include that heading row. So B49:B59 for me


Excel 2016
ABCDEF
46WATCHITRefer to TO for details
47
48TimeOutT/OOP1
49[ ]1:00LG2Prepare for LG2 Local goods - deps 3:30
50[ ]3:20LG1Local GoodsDriven in by Marabost
51[ ]3:303:30LG2Local GoodsDriven out by Marabost
52[ ]3:30LG1Local GoodsShunting
53[ ]5:225:31LP8DMU2Departs West Watchit
54[ ]5:55SF2StoneMake up Train - deps 6:15
55[ ]6:42SE1StoneDeparts Newton Abbot
56[ ]7:50LP11DMU2*** departs Tawnton
57[ ]8:42G80GoodsDeparts Bruton
58[ ]9:329:41LP16DMU2Departs West Watchit
59[ ]10:40LP17DMU2*** departs Tawnton
Stations


Given the above assumptions, does this work for you?
Code: 
Sub Sort_On_Time()
  With Worksheets("Stations")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Watchit_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Watchit_data")
      .Header = xlYes
      .Orientation = xlTopToBottom
      .Apply
    End With
  End With
End Sub
 
Upvote 0
Watchit_data is all columns & rows 48-59
Watchit_time is B49:B59

and I will play with your VBA code.

Ron
 
Upvote 0
Peter, all works as I have the macro working on 3 ranges on one sheet and one range on another until I get to the end it comes up with expecting End With but i do have one


With Worksheets("Stations").Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("Watchit_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("Watchit_data")
.Header = xlGuess
.Orientation = xlTopToBottom
.Apply
End With
End Sub
 
Upvote 0
Mabocat said:
.. it comes up with expecting End With but i do have one


With Worksheets("Stations").Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("Watchit_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("Watchit_data")
.Header = xlGuess
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Click to expand...
That's right, you do have one, but you should have two, see below. ;)

Also, the orange text should be on a separate line as below.

Peter_SSs said:
Rich (BB code): 
Sub Sort_On_Time()
  With Worksheets("Stations")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Watchit_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Watchit_data")
      .Header = xlYes
      .Orientation = xlTopToBottom
      .Apply
    End With
  End With
End Sub
Click to expand...


BTW, when posting code, please use Code Tags to preserve the indentations, otherwise the code is much harder to read & debug. My signature block below tells you how.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
timestamp listing to show more than >19hrs away per day?
Replies
6
Views
114
Kaper
Kaper
M
  • Question Question
#Value Error - Please Help
Replies
3
Views
66
Alex Blakenburg
A
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
659
libelouli
L
V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
114
djclements
djclements

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

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
Back
Top