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
OK done that Peter but it still fails, this is the full macro - would the Header being different than yours make the difference as I don't use header info for sorting
Code: 
Sub data_sort()
'
' data_sort Macro
'

    
       With Worksheets("Stations")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Charde_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Charde_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
    End With
    
    With Worksheets("Stations")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Marabost_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Marabost_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
          
    End With
    
        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
    
        With Worksheets("Tawnton")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Tawnton_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Tawnton_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
      
      End With
      
    End With
End Sub
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Mabocat said:
OK done that Peter but it still fails, this is the full macro - would the Header being different than yours make the difference as I don't use header info for sorting
Code: 
Sub data_sort()
'
' data_sort Macro
'

    
       [COLOR="#FF0000"][B]With [/B][/COLOR]Worksheets("Stations")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Charde_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    [COLOR="#FF0000"][B]With [/B][/COLOR].Sort
      .SetRange Range("Charde_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
    [COLOR="#FF0000"][B]End With[/B][/COLOR]
    
    With Worksheets("Stations")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Marabost_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Marabost_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
          
    End With
    
        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
    
        With Worksheets("Tawnton")
       .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("Tawnton_time"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
      .SetRange Range("Tawnton_data")
      .Header = xlGuess
      .Orientation = xlTopToBottom
      .Apply
      
      End With
      
    End With
End Sub
Click to expand...
You only fixed the two 'End With's in one of the four sections! All four sections have two With statements so they all need two End With statements. I Have highlighted this in one section above.
 
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