Unit allocation

[Elspeth Carter]

Hi everyone,

I'm struggling with a problem and any help would be great!

I have a list of 70 applicants that have made their 1st, 2nd and 3rd choices
for apartment units in a building. I need to give them their first choice
if available and if not their second choice and so on. They are sorted in
an order based on who applied first.

Any help or advice in terms of a formula that will save me doing this
process manually would be appreciated.

 

[JE McGimpsey]

One way:

Assume Names in Column A, Choices 1, 2, and 3 in columns B, C, and D,
respectively. In E1 enter:

E1: =B1

then enter this in E1 and copy down:

E2: =IF(COUNTIF($E$1:$E1,B2)=0,B2, IF(COUNTIF($E$1:$E1,C2)=0,C2,
IF(COUNTIF($E$1:$E1,D2)=0,D2,"Conflict")))