Sort by Column

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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