Using Excel for tournament

Good morning All,

I am attempting to use an excel sheet that contains the name (A1), rank (B1), and class (C1) for each entrant (84 total) of my competition. I want excel to plug entrants into brackets (Rooms) - located on another sheet - consisting of no more than 7 entrants per bracket. However, I need excel to delineate between two variables; rank, and instructor. The three possible ranks are Expert, Intermediate, Novice - I need an even mixture of each rank into each bracket.

If the information above has you completely confused, do not fret, it is my idea and I too am confused. Essentially what I want/need is the following. Each semester my department runs a speech tournament. We want to be able to type 1 list of participants, and have excel do all of the work as far as assigning each student to a room. We are trying to make sure there is an even mixture of rank (skill level) between the rooms, and would like to avoid students competing against fellow classmates. Is this idea even possible?

Any and all ideas/suggestions would be appreciated.

Thanks.