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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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

K
  • Question Question
conditional formatting question
Replies
2
Views
586
kerm007
K
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
651
libelouli
L
J
  • Question Question
Average of Two Columns Meeting a Specific Criterion
Replies
4
Views
199
JeffFinnan
J
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
71
stretch2011
S

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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