# Sort by Column

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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:
Last edited:
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.

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.

Hi Special-K99, it is not working.

Can I send you an excel sheet?

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.

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.

Replies
0
Views
465
Replies
3
Views
130
Replies
1
Views
212
Replies
19
Views
609
Replies
0
Views
203

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

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

### Which adblocker are you using?

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

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