Sorting two columns

[Mabocat]

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

 

[AlphaFrog]

-Select all three columns
-Select from the menu Data\Sort
-Sort by the Time column

Those steps can be recorded to a macro.

 

[Mabocat]

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]

 

[sandy666]

maybe with PowerQuery:

[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]

 

[Mabocat]

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

 

[sandy666]

This is not VBA !
It's M-code for PowerQuery aka Get&Transform

Getting Started with Get & Transform in Excel
Microsoft Power Query for Excel Help

 

[Mabocat]

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

 

[sandy666]

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

I did it with PowerQuery but I can't say it can be done with VBA. I don't like VBA. Sorry

 

[Peter_SSs]

.. 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.

Try this with a copy of your workbook.
(Assumes data in columns A:C with column D empty)

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

 

[Mabocat]

I did it with PowerQuery but I can't say it can be done with VBA. I don't like VBA. Sorry

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.