Sort by Column

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Use a Helper column.

in a blank column say column I

in I1
=A1+0.1

in I2
=IF(A2="",C1+0.1,A2+0.1)
and copy this formula only down the column and include one more row which will be blank (so that all data consist of 3 rows - even a blank row)

Now sort on Column I.
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
in I3 and subsequent rows copy I2.
Copy as far as there is data in column B and one more row.
This ensures that all the data consist of three rows - two with data and one blank row, otherwise when you sort it doesnt produce a blank row on one row of data.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Sorry, my fault, the formula needs adjusting, i was testing in column A.

in a blank column say column I

Start your data in row 2

in I2
=IF(C2="",I1+0.1,C2+0.1)
and copy this formula only down the column and include one more row which will be blank (so that all data consist of 3 rows - even a blank row)

Now sort on Column I.
 

emvella

Board Regular
Joined
Apr 7, 2017
Messages
63
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi Special-K99, it is not working.

Can I send you an excel sheet?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Sure, I dont know if you can send an attachment using PM.
Might be easier to upload to an online storage site and then provide the link to it.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
Aarrrgh! You've changed the data again.
In Post #5 the times are in column C, now they're in column B!! Grrr!!

Ok.

Insert a row so the data starts in row 2.

in H2
=IF(B2="",H1+0.1,TEXT(B2,"hhmm")*100+0.1)
copy this formula down as far as row 16 (the Blank row - this ensures each data entry has THREE lines (5*3=15) + the blank row at the top = 16)

Now select columns B:H and sort on column H

The times are now in order and each accompany row has been sorted with its original "parent" time.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,297
Messages
5,836,479
Members
430,434
Latest member
whatabout

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
Top