Sort automatically when a file is open

**metricspace** · 10-26-2009, 03:07 AM

Hi,

I've prepared an attendance sheet that has a column for batch timing, batch timings are as follows
7-9 (mean 7AM to 9 AM)
9-11
11-1
2-4
4-6
6-8
8-10

When i sort batch timing (ascending) it get sorted as 11-1, 2-4 , 4-6 ...

My queries are
1) can we sort the column batch timing as 7-9, 9-11, 11-1 .. and so on.
2) whenever i open the sheet is should automatically sort the column batch timing.

any help/hint is greatly appreciated.

Thanks,
metric

**Palmetto** · 10-26-2009, 08:19 AM

You cannot get the sort results you want the way your data is entered due to way Excel processes the data.

You will have to either:
Break the times up into separate columns and sort ascending on the start time
e.g. A1 = 7am, B2 =9am -- then sort on column-A

Or . .

Leave the original column as is and use a helper column to provide a value on which to sort. This could be a formula such as:

Assuming row1 is header and value are text
=IF(MID(A2,2,1)="-",LEFT(A2,1),LEFT(A2,2))+0

This will return the first number and provide the means to get the sort result you want.

This bit of code will sort the sheet when the workbook is opened. It will have to be adapted per your requirements for sheet and range references.

Please Login or Register  to view this content.

Note: because the code executes when the file is opened, the data will already be sorted when you view the attachment. Manually rearrange the data in column-A, close the file and reopen it to see the sorting code at work.

**metricspace** · 10-26-2009, 02:33 PM

Thanks for a quick and helpful response.

I first thought of separate column for 7- and 9 , however that would not have allowed me to filter the data for 7-9 batch.

The helper column did the my work. I created a helper column next to time column and feeded it if with conditon
if(A2="7-9",1,if(A2="9-11",2 and so on till last batch 8-10)))

now each batch had its number listed in the helper column as 1, 2 ...7.

I got the output I wanted by sorting helper column (which i named as batch number).

Thank you so much for the help.

metric

**Palmetto** · 10-26-2009, 07:23 PM

The helper column did the my work. I created a helper column next to time column and feeded it if with conditon
if(A2="7-9",1,if(A2="9-11",2 and so on till last batch 8-10)))

This sounds like a lot of nested IF functions. The formula I provided is much simpler (more efficient) and returns the results you need for sorting.

If you're satisfied with the solution, please mark your thread as solved and remember to add to a users reputation (see how in my signature).