How to get the best of 3 choices according to the highest number?

[studentlearner]

Can i check how do i get the final choices from this table to the second table?

Employee Name	Attendance Percentage	Choice1	Choice2	Choice3
Alan	75.09333333​	Project 1	Project 3	Project 4
John	73.37​	Project 1	Project 4	Project 2
Alan	73.15​	Project 3	Project 5	Project 6
Chris	73.15​	Project 2	Project 4	Project 1
Tommy	70.63666667​	Project 2	Project 6	Project 5
Jack	70.63666667​	Project 6	Project 1	Project 3

the second table:

Employee Name	Attendance Percentage	Choice1	Choice2	Choice3	Final Choice
Alan	75.09333333​	Project 1	Project 3	Project 4	Project 1​
John	73.37​	Project 1	Project 4	Project 2	Project 4​
Alan	73.15​	Project 3	Project 5	Project 6	Project 3​
Chris	73.15​	Project 2	Project 4	Project 1	Project 2​
Tommy	70.63666667​	Project 2	Project 6	Project 5	Project 6​
Jack	70.63666667​	Project 6	Project 1	Project 3	NIL

So it kind of checks the above cell and views whether it was taken and if it was taken it views the second choice and if it was taken it would check the third choice if all choice is taken it will display NIL. Any help with the formula or through VBA would be a great help. Thank you!

 

[Dermot]

Try this VBA

To use it, put this in a cell = AllocateProjects(SelectionTable)
where SelectionTable is the table of choices, ie the three columns headed Choice 1, Choice 2 and Choice 3, but excluding those headings, ie just the table of people's choices
So if Alan's first choice was in D4 (say), then the range would be D4:F9

Function AllocateProjects(rngProjects As Range)
  Dim r, c, i, j, arrOut, UsedProjects, v
  r = rngProjects.Rows.Count
  c = rngProjects.Columns.Count
  ReDim arrOut(1 To r, 1 To 1)
  For i = 1 To r
    For j = 1 To c
      v = rngProjects.Cells(i, j)
      If InStr(UsedProjects, v) = 0 Then
        arrOut(i, 1) = v
        UsedProjects = UsedProjects & "&" & v
        Exit For
      End If
    Next j
    If arrOut(i, 1) = "" Then arrOut(i, 1) = "NIL"
  Next i
  AllocateProjects = arrOut
End Function

 

[Peter_SSs]

So it kind of checks the above cell and views whether it was taken and if it was taken

Firstly, I am assuming those two Alans are different people. Why can't the second Alan have his first choice since it was not taken by the first Alan or by John?
Similarly, why can't Chris have his first choice?

To my logic, this is a direct worksheet formula solution

21 11 11.xlsm
	A	B	C	D	E	F
1	Employee Name	Attendance Percentage	Choice1	Choice2	Choice3	Final
2	Alan 1	75.09333333	Project 1	Project 3	Project 4	Project 1
3	John	73.37	Project 1	Project 4	Project 2	Project 4
4	Alan 2	73.15	Project 3	Project 5	Project 6	Project 3
5	Chris	73.15	Project 2	Project 4	Project 1	Project 2
6	Tommy	70.63666667	Project 2	Project 6	Project 5	Project 6
7	Jack	70.63666667	Project 6	Project 1	Project 3	NIL
Choices

Cell Formulas
Range	Formula
F2:F7	F2	=INDEX(FILTER(C2:E2,ISNA(MATCH(C2:E2,F$1:F1,0)),"NIL"),1)