excel formula and vba automation help

[clapin]

I have attached a dummy spreadsheet with three tabs.


The first tab has a schedule of corporates and their meetings date and time with equity investors including the total number of persons in the room for each meeting.


The second tab has a list of rooms along with the maximum capacity of the number of people that could be accomodated in these rooms.


I am looking to allocate these corporates from the schedule tab meeting rooms based on their schedule and that they meet a few conditions which I have listed in each of the tabs in the spreadsheet I have attached.

I have listed all the conditions in the sheet..

[clapin]

any help with the conditions written in italics in the excel sheet will do too. any ideas. anything at all

[SamT]

Best bet is to place all known details on worksheets, then VBA can easily work with the details and provide a good Assignments schedule for you

[clapin]

Hello Sam,

Thank you for taking the time to reply - updated spreadsheet with almost all formula's for the Schedule sheet attached.
Recorded Macro in the project explorer. Any help appreciated

Thank you, again.

21212

[clapin]

See tab three (titled room details).



I want to assign each corporate in column H a room from column B based on the maximum capacity of the room(column D) and the number of persons in the room (column I) and the alphabetical order of the corporates

If we forget the alphabetical order for now it is fine.

So ideally if I have to do this manually,

AB INBEV will be room 102
British Land will be room theatre
Capgemini would be room borsino and
Deloitte will be room 101

Is there any array formula that can accomplish this ?

Any help - much appreciated!

[clapin]

to simplify my question, read below


I want to assign each corporate a room based on the maximum capacity of the room and the number of persons in the room

So as an example
THESE ARE 4 CORPORATES WITH THE NUMBER OF PEOPLE IN EACH MEETING
AB INBEV 10
British Land 4
Capgemini 9
Deloitte 25



THESE ARE 4 ROOMS WITH THEIR MAX CAPACITY
101 26
102 18
BORSINO 9
THEATRE 8




So ideally if I have to do this manually,

AB INBEV will be room 102
British Land will be room theatre
Capgemini would be room borsino and
Deloitte will be room 101

Is there any array formula that can accomplish this ?

Any help - much appreciated!

[SamT]

I combined both your threads regarding this question into one thread.