Help to select names from list

[chacale]

Hi wizards,

I need some help for assigning colleagues to specific patients.... so the scenario:

1) i have names (1 to 11 different ones), not repeated, within C5:C15, also some blank cells;
2) i have in H6:H35 same names that can be in C5:C15 (no new names) that are distributed randomly,
3) i want to generate a function that for I6:I35 randomly selects one of the names in the C5:C15 while excluding the corresponding one in the H column; should be able to drag the column down in I, but keep fixed the C5:C15 range.
4) ideally the formula does not refresh everytime info is entered, file saved, etc. So the assignements (new column I) can be accessed and queried anytime in future.

Thanks so much for this help!!!


Ale

[sandy666]

maybe any example excel file with before data and after expected result manually created