replacing nested if statements with some kind of VBA loop?

I have a worksheet where there are 16 column (each one has a student's major for a single semester).
I want to be able to do the following:

1) This part I have already, but it's an ugly brute force solution that can't be generalized to the future cases: For each row, pick the first column where the cell is not blank, and where it is different from the major listed in the first column and return both the column number of this column and its contents (the second major), or if this never happens return a blank cell. Here's how I did that:

Ugly brute force formula to return second major if there ever was a major change:
=IF(NOT((D2=0)+(D2=C2)),D2,IF(NOT((E2=0)+(E2=C2)),E2,IF(NOT((F2=0)+(F2=C2)),F2,IF(NOT((G2=0)+(G2=C2)),G2,IF(NOT((H2=0)+(H2=C2)),H2,IF(NOT((G2=0)+(G2=C2)),G2,IF(NOT((H2=0)+(H2=C2)),H2,IF(NOT((I2=0)+(I2=C2)),I2,IF(NOT((J2=0)+(J2=C2)),J2,IF(NOT((K2=0)+(K2=C2)),K2,IF(NOT((L2=0)+(L2=C2)),L2,IF(NOT((M2=0)+(M2=C2)),M2,IF(NOT((N2=0)+(N2=C2)),N2,IF(NOT((O2=0)+(O2=C2)),O2,IF(NOT((P2=0)+(P2=C2)),P2,IF(NOT((Q2=0)+(Q2=C2)),Q2,IF(NOT((R2=0)+(R2=C2)),R2,"")))))))))))))))))


Formula to return the column in which the second major appears:
{=IF(U2="","",MIN(IF(D2:R2=U2,D$1:R$1)))}

I'll call this value T_1

2) Repeat this process to find times when students changed their major a second time. This is tricky, though, because I can't used the nested if statement approach this time, since the place in the array where the nested if statement procedure should begin and end is dependent upon the semester in which the student first activated their second major. Logically, what I want is something like this to give me the third major that a student may have chosen:

Let n=T_1+1 (where T_1 is the column number where the second major first appears)
If the value in the cell in column n is not (blank or the same as the cell in column T_1), then return the text (major) in that cell, otherwise, let n=n+1 and repeat, for all n=T_1+1,T_1+2,...,16. If we get to n=17, terminate this loop and return a blank cell.

And then similarly something like this for the column in which this third major first appeared:
Let n=T_1+1 (where T_1 is the column number giving the first major that differs from their initial major)
If the value in the cell in column n is not (blank or the same as the cell in column T_1), then return n, otherwise, let n=n+1 and repeat, for all n=T_1+1,T_1+2,...,16. If we get to n=17, terminate this loop and return a blank cell.

I'll then need to repeat this process for the fourth new major and the fifth new major, etc, but once I have the code for the third new major, extending it to these cases would be obvious...

I assume I can't do this with formulas in excel and will need to do some kind of VBA code, but I really don't have much experience in that, and attempts at googling for the past few days haven't really yielded anything useful. I was hoping someone could help me to construct the necessary code for this (and in the best case scenario help me understand how it works ). I'm a mathematician, so logic concepts are all really clear to me, but my programming experience is very spotty, and therefore I often don't know how to practically implement logical processes in a particular computer code...

Thanks for taking the time to read my post!

Originally Posted by tigeravatar

What happens if a major starts as PCN, changes to NUR, and then changes back to PCN?
So that the changes would be PCN -> NUR -> PCN
Or is that not a possible scenario?

Yes, that can also happen, and in that case, I'd want the formula to return PCN as the first major, NUR as the second major, and then PCN as the third major...

tigeravatar, thank you so much - this totally solves my problem!
I can't thank you enough!!

@ cwyn

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.