Sort Not Busy Clerks

Microsoft Excel 2003 (full)
June 6, 2010 at 12:16:39
Specs: Windows XP
I need help writing an Excel VBA routine.

I have 10 clerks that are assigned tasks in rotation. When a task is completed, I want the next available clerk that hasn’t completed a task for the longest period to be assigned to the next task. I also record the task type, start time and completion time in other parts of the spreadsheet.

To code this process, I envision a queue (array) with clerk numbers and the time they completed their last task. The queue could then be sorted by time in ascending sequence. Another column may be needed to distinguish between busy and not-busy clerks. Using this, I could just display the not-busy clerks in the desired sequence.

I wish to trigger this process every time the completion time is entered into the completion time column.

See More: Sort Not Busy Clerks

Report •

June 6, 2010 at 17:07:22
You don't need VBA just to determine the Clerk with the longest Idle Time.

This table shows that Steve has been idle the longest:

      A      B           C        D          E
1  Name	 Time Done   Time Now	  Idle	   Longest Idle
2  Bob	 3:45 AM     7:46 PM    16:01:06     Steve
3  Sue	 3:44 AM     7:46 PM  	16:02:06	
4  Steve 3:42 AM     7:46 PM	16:04:06	
5  Mary	 3:47 AM     7:46 PM	15:59:06	

Column A is manual entries of Names.

Column B is manual entries of Completed Task Times (shortened to Time Done for this post) and is formatted as Time...1:30 PM.

Column C contains =NOW() and is formatted as Time...1:30 PM.

Column D contains =C2-B2 (drag down) formatted as Custom...h:mm:ss.

E2 contains:


LARGE(D2:D5,1) will evaluate to 16:04:06 (in this example)

MATCH(LARGE(D2:D5,1),D2:D5,0) will evaluate to 3 which will be used as the row_num for the INDEX function.

=INDEX(A2:A5,MATCH(LARGE(D2:D5,1),D2:D5,0),1) will be equivalent to

=INDEX(A2:A5,3,1) which will return Steve.

Each time you enter a time in Column B, the values will update and the name with the longest Idle Time will appear in E2.

If you want a list of Clerks "automatically" ranked by Idle Time, Longest to Shortest, for the table above, put this in E2 and drag it down:


The ROW()-1 function will evaluate to 1 then 2 then 3 etc. and be used as the k argument for the LARGE function.

BTW...All of the Idle Times will update whenever the sheet calculates, but the name won't change because everyone's Idle Time will update by the same amount.

Report •

June 6, 2010 at 18:25:43
PHENOMENAL! Absolutely brilliant! Thank you so very much. Although, I just discovered an issue. If 'time done' has a match (2 names have the same time), the 'Clerks "automatically" ranked by Idle Time' option shows 1 name twice and omits a name. ? ?

Report •

June 8, 2010 at 06:58:33
The reason you get duplicate names is that just about all of Excel's "lookup functions" stop when they find the first occurrence of a value.

When the LARGE function finds the same Idle Time value for more than one person, the INDEX(MATCH...) combination only returns the first occurrence of that value.

There are 2 ways to fix that:

1 - Make your Time Done entries more precise:
3:47:34 instead of just 3:47. I doubt that 2 clerks ever complete their task at the exact same second.

2 - Force the Idle Time to be slightly different by dragging something like this down that column:


This will ensure that the Idle Times are always slightly different so you'll never get a match. The drawback is this:

Let's say Row 2 and Row 5 are a tie. The person in Row 5 will appear to have been idle longer because 5*0.00000001 is greater than 2*0.00000001. In other words, for every tie, the person farthest down the list will be appear to have been idle longer.

Use whichever option you think is more fair to your employees.

Report •

Related Solutions

June 8, 2010 at 17:51:57
Again, I thank you very much.

Report •

Ask Question