Auto-Assigning a (random) Name from list of values

[G. Richardson]

Hello. Does anyone know of a way without VBA to tell Excel to select names,
either randomly or in sequence, from a column and "assign" the name to a



I have only basic Excel experience, but because I am the most tech savvy
within my group, I've been left in charge of a project to help assign
students to the correct tutors.

Here's an important thing that I want to mention. I am on a Macintosh, and
this latest 2008 version of Excel does NOT support VBA. It puts a bit of a
crutch on what I wanted to do, but I still believe it is possible.

I have three main columns in the document I am struggling to create. Column
A has a list of Topics, Column B has the students name, and Column C is
suppose to randomly assign a tutor who is familiar with the subject matter.


My concept for this (I wish there was a way to attach it) is to have a
column on another sheet (called "Teachers") for each group of tutors with the
particular specialty.

I am trying to get Excel to look at the topic from Column A, and use that to
tell it which column on Sheet 2 to assign the tutor from.

So far, with my exploration all I have managed to discover was this
Index/randbetween function.

=INDEX(Teachers!A3:A9,RANDBETWEEN(1,COUNTA(Teachers!A3:A9)),1)

one I came across on the Microsoft forum has the same limitation
=VLOOKUP(RAND()*MAX(A:A)+1,A:B,TRUE)

It randomly chooses a name from a column and displays it. I know it is not
the function I need. It will recalculate each time a value is added to the
sheet. It will also not guard against double booking a person. And it will
also not choose between lists of specialties. But my limited formula
experience is preventing me from discovering what *will* do it correctly.

I've seen threads like this one for making random lists. It gives me the
idea to use vlooku
http://www.mrexcel.com/forum/showthread.php?t=260235&highlight=select+random+name

and this one, which takes a random sample without any repeat
http://www.mrexcel.com/forum/showthread.php?t=267244&highlight=select+random+name

The second link uses VBA, and I do not quite follow the method used in the
first link.

I am eager for any advise or brainstorming of any suggestions for new ways
to solve this particular challenge.

 

[Don]

What about having a column in the other sheet for subjects and tutors, then
in the main one look at the subject for the student and to a random lookup in
the right column? You can have another list for the name and teacher and do
a vlookup? I think you would still have to do a pivot table on the student /
tutor to make sure you do not have any mult or ratio?

 

[Al]

Need a little more information. Do you have the same number of teachers as
students, and do the teachers only teach on topic?