Sorting blocks of rows by a date and time range heading

AJK5363

New Member
Joined
Jun 30, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
I have been given lists from various sources of Zoom courses and activities I could take part in while in lock down. I have initially had to extract this information from PDF files and copy into a Excel worksheet.
The problem I have is that nothing is in date / time order but just blocks of information in rows where the header row for each block is in the format dd/mm/yyyy,hh:mm-hh:mm where hh:mm is the start and end time of the course.
So as in the attached example I need to be able to sort on the line containing the date / time etc. but keeping the information in the rows below that together. Everything is in Column A. The Title is always in one row. The Zoom ID is always in one row. The Zoom password is always in one row. The Notes though can be over a various number of rows.

This is a one off task but it's a very long list.

Is there a way of fairly easily doing this or would it need a macro?

Any help appreciated.

Capture.JPG
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you give us the sample data again with XL2BB to make it easier for helpers by not having to manually type out sample data to test with?
 
Upvote 0
I think this should do it for you, assuming that there are no cells that contain a number and nothing else.

Insert a new row 1 if there is not something above that first data/time.
Use this formula copied down.

Cell Formulas
RangeFormula
B2:B17B2=IF(ISNUMBER(LEFT(A2,FIND(",",A2&",")-1)+0),LEFT(A2,FIND(",",A2)-1)+REPLACE(LEFT(A2,FIND("-",A2)-1),1,FIND(",",A2),""),B1)



Now if you sort both columns using column B as the sort criteria then the blocks should sort by date/time and the other cells in each block should hole their relative positions.
Here it is after the sort:
 
Upvote 0
Hello AJK5363,

I would use Power Query to un-stack the data. If available for you, all the steps are done here via the GUI and mouse actions. No coding required. Even the step where I used the function "Text.Contains" in a manual formula, can be done by selecting Add Conditional Column and use the drop down selections to make the same formula.

Taking the liberty to use the data sample made by @Peter_SSs it would look like this
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    FilterOutNull = Table.SelectRows(Source, each ([Data] <> null)),
    AddIndex_From1 = Table.AddIndexColumn(FilterOutNull, "Index", 1, 1),
    GetDateTimeString = Table.AddColumn(AddIndex_From1, "DateTime", each if Text.Contains ([Data], "/") then [Data] else null),
    #"FillDown°DateTimeString" = Table.FillDown(GetDateTimeString,{"DateTime"}),
    #"SplitDateTimeBy"",""" = Table.SplitColumn(#"FillDown°DateTimeString", "DateTime", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"DateTime.1", "DateTime.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"SplitDateTimeBy"",""",{{"DateTime.1", type date}, {"DateTime.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DateTime.1", "Date"}, {"DateTime.2", "Schedule"}}),
    SortRowsDateAsc_ScheduleAsc_IndexAsc = Table.Sort(#"Renamed Columns",{{"Date", Order.Ascending}, {"Schedule", Order.Ascending}, {"Index", Order.Ascending}}),
    RemoveIndex = Table.RemoveColumns(SortRowsDateAsc_ScheduleAsc_IndexAsc,{"Index"})
in
    RemoveIndex
1588939101338.png
 
Upvote 0
I think this should do it for you, assuming that there are no cells that contain a number and nothing else.

Insert a new row 1 if there is not something above that first data/time.
Use this formula copied down.

Cell Formulas
RangeFormula
B2:B17B2=IF(ISNUMBER(LEFT(A2,FIND(",",A2&",")-1)+0),LEFT(A2,FIND(",",A2)-1)+REPLACE(LEFT(A2,FIND("-",A2)-1),1,FIND(",",A2),""),B1)



Now if you sort both columns using column B as the sort criteria then the blocks should sort by date/time and the other cells in each block should hole their relative positions.
Here it is after the sort:
Many thanks Peter - This has worked perfectly.

Also Thank you to GraH - I will have to learn about about a Power Query.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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