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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
-Select all three columns
-Select from the menu Data\Sort
-Sort by the Time column

Those steps can be recorded to a macro.
 
Upvote 0
I did that and it ended up like this putting the blank time last which is wrong as i want the Out time to fit into the overall time scale
[TABLE="width: 175"]
<colgroup><col><col><col></colgroup><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]5:22[/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:42[/TD]
[TD] [/TD]
[TD]SE1[/TD]
[/TR]
[TR]
[TD]7:50[/TD]
[TD] [/TD]
[TD]LP11[/TD]
[/TR]
[TR]
[TD]7:52[/TD]
[TD] [/TD]
[TD]LG1[/TD]
[/TR]
[TR]
[TD]8:42[/TD]
[TD] [/TD]
[TD]G80[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]7:52[/TD]
[TD]LG2[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
maybe with PowerQuery:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Out", type time}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Time] = null then [Out] else [Time]),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Ascending}, {"T/O", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom"})
in
    #"Removed Columns"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Time[/td][td=bgcolor:#5B9BD5]Out[/td][td=bgcolor:#5B9BD5]T/O[/td][td][/td][td=bgcolor:#70AD47]Time[/td][td=bgcolor:#70AD47]Out[/td][td=bgcolor:#70AD47]T/O[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
03:57​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LG2[/td][td][/td][td=bgcolor:#E2EFDA]
03:20:00​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]LG1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
03:20​
[/td][td][/td][td]LG1[/td][td][/td][td]
03:57:00​
[/td][td][/td][td]LG2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
06:27​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LG1[/td][td][/td][td=bgcolor:#E2EFDA]
05:22:00​
[/td][td=bgcolor:#E2EFDA]
05:31:00​
[/td][td=bgcolor:#E2EFDA]LP8[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]
06:27​
[/td][td]LG2[/td][td][/td][td]
05:55:00​
[/td][td][/td][td]SF2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
05:22​
[/td][td=bgcolor:#DDEBF7]
05:31​
[/td][td=bgcolor:#DDEBF7]LP8[/td][td][/td][td=bgcolor:#E2EFDA]
06:27:00​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]LG1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
05:55​
[/td][td][/td][td]SF2[/td][td][/td][td][/td][td]
06:27:00​
[/td][td]LG2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
06:42​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]SE1[/td][td][/td][td=bgcolor:#E2EFDA]
06:42:00​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]SE1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
07:50​
[/td][td][/td][td]LP11[/td][td][/td][td]
07:50:00​
[/td][td][/td][td]LP11[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
08:42​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]G80[/td][td][/td][td=bgcolor:#E2EFDA]
08:42:00​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]G80[/td][/tr]
[/table]
 
Upvote 0
OK, I think I understand a little bit of that - though not sure why this part is included
{"T/O", Order.Ascending}})



Looks like I will have to obtain a book on Excel VBA to work out what each word means - i was very happy with the older style excel 4 macros before they changed to VBA





 
Upvote 0
Ah thanks so I assume it can't be done with either the old macro language Excel 4 or VBA then ?

May have to look at the spreadsheet format

Ron
 
Upvote 0
Mabocat said:
.. 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.
Click to expand...
Try this with a copy of your workbook.
(Assumes data in columns A:C with column D empty)

Code: 
Sub Reorder()
  Application.ScreenUpdating = False
  With Range("A2:D" & Range("A" & Rows.Count).End(xlUp).Row)
    .Columns(4).FormulaR1C1 = "=MIN(RC[-3],RC[-2])"
    .Sort Key1:=.Columns(4), Order1:=xlAscending, Header:=xlNo
    .Columns(4).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
sandy666 said:
I did it with PowerQuery but I can't say it can be done with VBA. I don't like VBA. Sorry
Click to expand...

I had quick look at Powerquery. Do i assume one has to download the system to be used? I want to be able to have all the macros as part of the Excel file so I can send it to others to use.
 
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
112
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,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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