Pick the first value in every row that has not been picked yet

Question

In the example I am giving below, I would like the result in Column G to be what is in Column C, unless it already exists in Column G in rows above it:

These rows represent students' choices for topics (C is their 1st choice, D is their 2nd choice, etc.) and each topic needs to be unique. The first student gets their first choice, and then no one else can choose it. If another student in a successive row has chosen that as their first choice, then that student will have to be assigned their 2nd choice from Column D instead.

I have contemplated somehow using VLOOKUP or FILTER or even IF statements, but with 250 students/rows, that could get cumbersome.

What is the best way to go about this?

Answer 1

I think I figured it out myself. This works for 5 columns, but I could add more by extending the formula.

=if(isna(C12=vlookup(C12,$G$2:G11,1,false)),C12,if(isna(D12=vlookup(D12,$G$2:G11,1,false)),D12,if(isna(E12=vlookup(E12,$G$2:G11,1,false)),E12,if(isna(F12=vlookup(F12,$G$2:G11,1,false)),F12,""))))     

Answer 2

A shorter formula, which works the same for any number of columns, is

=array_constrain(filter(C2:F2, isna(vlookup(C2:F2, G$1:G1, 1, false))), 1, 1)

(The formula in G2 shown; it should be copied down the G column). Explanation:

• isna(vlookup(C2:F2, G$1:G1, 1, false)) is True for the values that have not appeared above in column G.
• filter(C2:F2, ...) filters the array C2:F2 by the above criterion
• array_constrain(..., 1, 1) returns the first element of the array.

Sample output (in G column):

+---+---+---+---+---+
| C | D | E | F | G |
+---+---+---+---+---+
| q | w | e | r | q |
| e | q | w | e | e |
| q | e | d | e | d |
| e | s | v | h | s |
| q | d | s | r | r |
+---+---+---+---+---+