automatic inserting the values
 

In column A there is a list of dates.
In columns B-E there are four types of assignments ( I, II, III, IV).
In columns F-I there are list of persons available for each assignment
(some persons can do more assignments but not on the same day).

A
Dates
6/11
6/12
6/13
6/14
6/15
....


F
I
John
James
Bill
Michael

G
II
James
Bill
Bob
Andrew

H
III
Patrick
Michael
Andrew
Justin

I
IV
James
Michael
Justin
Bill


Problem:
1st Step: First I manually define persons for assignment IV (column
E).
2d Step: For column D I want to use a formula which would
automatically pick the name from the list (column H), check if a
person on this date already exists in a column E (if it does, then
pick next name in column H) and then insert the name in the cell for
each day.
3d and 4th Step: The same principle applies when I want automatically
insert names for assignment I and II (columns B and C). For column C,
check cells in column D and E. For column B, check cells in column C,
D and E.

There is another requirement: I want to set the formula to always pick
the name which has not been used for the longest time on the
assignment.

Thank you for help,
stremetzky