replacing nested if statements with some kind of VBA loop?

cwyn,

Attached is an example workbook based on the criteria you described.
In cell T3 and copied over and down to X6 is this array formula (note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter):

{=IFERROR(INDEX($C3:$R3,SMALL(IF($C3:$R3<>"",COLUMN($C3:$R3)-2),COLUMNS($S3:S3))),"")}

Is something like that what you're looking for?

Hi tigeravatar,
Thanks so much for the help! This is really close, but doesn't quite do what I want, since often the major is listed for several semesters before it changes (and sometimes it's blank in between), for example, like this:

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16		1st	2nd	3rd
MAT		MAT	MAT		NUR		NUR	SCI				SCI					MAT	NUR	SCI

Is there a way to modify your array formula so that it also works in this case?

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?

That brute force equation gave me like 9 migraines trying to figure it out. So i'm gonna pass at attempting to solve this problem, but I'm interested in the solution.

cwyn,

Attached is version 2. It uses a UDF I created for this purpose. The UDF is named ListChange and takes the following two arguments:
varData: This is a required argument that can be an array or range of cells. varData is used to determine where the list changes occur.
[ChangeIndex]: This is an optional argument that specifies which list change you want returned. If omitted, the function will return the first list change (in your example, it would return MAT because that is the first item). The function treats this as a change from "nothing" to "something".

If the function cannot determine a list change (either because there isn't one, or you requested a list change value with an invalid ChangeIndex parameter, for example you might have specified the 2nd argument as "a" which is invalid, or when there are only 3 changes and you specify a ChangeIndex of 4), it will return the #VALUE! error.

In the attached .xlsm file in cell T3 and copied right is this formula that utilizes the UDF:

=IFERROR(ListChange($C3:$R3,COLUMNS($S3:S3)),"")

[EDIT]:
Added a .xls version in the attachments section. In the .xls file, in cell T3 and copied right is this formula that utilizes the UDF:

=IF(ISERROR(ListChange($C3:$R3,COLUMNS($S3:S3))),"",ListChange($C3:$R3,COLUMNS($S3:S3)))

Here is the code used for the UDF:

Public Function ListChange(ByVal varData As Variant, Optional ByVal ChangeIndex As Long = 1) As String
    
    Dim varIndex As Variant
    Dim arrList() As String
    Dim strList As String
    Dim strTemp As String
    
    If IsArray(varData) _
    Or TypeName(varData) = "Range" _
    Or TypeName(varData) = "Collection" Then
        For Each varIndex In varData
            If Len(varIndex) > 0 And LCase(varIndex) <> LCase(strTemp) Then
                strList = strList & "||" & varIndex
                strTemp = varIndex
            End If
        Next varIndex
        strTemp = vbNullString
        arrList = Split(strList, "||")
        On Error Resume Next
        strTemp = arrList(ChangeIndex)
        On Error GoTo 0
        Select Case (Len(strTemp) > 0)
            Case True:  ListChange = strTemp
            Case Else:  ListChange = CVErr(xlErrValue)
        End Select
    Else
        Select Case (ChangeIndex = 1)
            Case True:  ListChange = varData
            Case Else:  ListChange = CVErr(xlErrValue)
        End Select
    End If
    
End Function

How to use a UDF:

1. Make a copy of the workbook the UDF will be stored in
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. The UDF is now available for use

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save