Desk allocation

[Seaglass]

Hi

I'm sure this is simpler than I'm finding. I have four columns of data which the number of rows vary from week to week. The data set is normally multiple days.

Date | Name | Start | End

The thing...
I want to allocate each person a desk however I want it to check the end time of people already assigned a desk so if the person given desk 2 finishes at 3pm I can reuse that desk for someone starting later than that. All people start after 8am and finish before 8pm so there is no instances of someone working across multiple days; although they will work multiple days.

Any ideas? I'm presuming any formulas using "IF" will be long and I dont know enough about VBA to start writing it from scratch, only enough to read through it and make sense of what's happening.

Thanks

 

[Col Delane]

Hi Seaglass and welcome to the Forum

I'm not clear about the layout of your sheet and how you will populate it.


Questions:

1. Where does the Desk No. appear?
   
   
2. Are Start and End times?
   
3. Are desks booked out for minimum time blocks (e.g. 30 min, 1 hour)?
   
4. For what period is the data - just one fixed week, a rolling week, or ???
   
   
5. What is it that you're wanting a formula (or VBA) to actually do?
   
6. Is Excel the right tool for the application?

Could you rearrange your table of data such that it looks like a basic appointment diary:

Date | Start | End | Desk 1 | Desk 2 | Desk 3 |Desk 4 |Desk 5
22/2 | 8:00 | 8:30 | John D | _____ | _____ | Bill G_ |_____
22/2 | 8:30 | 9:00 | John D | Julia R | _____ | Bill G_ |_____
22/2 | 9:00 | 9:30 | John D | Julia R | _____ | _____ |_____
22/2 | 9:30 | 10:00 | John D | _____ | Clint E | _____ |_____
22/2 | 10:00 | 10:30 | John D | _____ | Clint E | _____ |_____
and so on

If the data set is long you could then use Autofilters to check any desk for vacancies.