4

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:

Example spreadsheet

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?

1
  • Besides the print screen, create a demo spreadsheet, share it with anyone with the link for view only and add it to the question. – Rubén Feb 5 '16 at 22:48
2

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,""))))     
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 |
+---+---+---+---+---+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.