Showing Custom Value Based on the Condition of Dynamic Table

[DocAElstein]

Hi,
I think your formula attempt looks like what I would have come up with when I first started using Excel.
I think I might be able to come up now with a slightly better formula, but it would not be much better.
( I had a worksheet full of many very long formulas like yours when I first started using Excel )

Now, a few years later, I personally prefer VBA, and I have not advanced much in my formula capabilities. But this is just my personal choice. At some of the other forums there are some extremely competent formula experts who could probably give you a very good formula solution. Some of those experts hate VBA.
It is all just personal choice of what you feel most comfortable with.

I think as time goes on you will decide yourself whether formulas, VBA, or some combination of the two are what you prefer.

I am not a computer expert. I am not very good at Maths. I find difficult formulas very hard to understand. I find VBA coding much easier.
I can’t help you much with a formula solution

( It would be interesting to see a good formula solution, for comparison. I can’t give you such a solution. If this was my project, I might try to get a formula solution, possibly from one of the other forums, just for comparison, if I had the time.
But its up to you. I think the best formula people are posting just now at mrexcel.com and eileenslounge.com.
excelforum.com also has some very good formula experts, but they seem to be taking a break from posting just now) )

---

This macro follows my original logic ideas. ( I am using a slightly different logic for the array, arrDec() , just because it was convenient to use True for a non decreasing row, and False for a decreasing row. Using your sample data , arrDec()= { False , True , False }

Public Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long, Lc As Long
 Let Lr = Me.Range("B" & Me.Rows.Count & "").End(xlUp).Row
 Let Lc = Me.Cells(4, 2).End(xlToRight).Column             '  I am using a slightly less common way including   xlToRight   because there are some explanation wordings that would be found giving a false number by the more typically used    Columns.Count xlToLeft   way
Dim RngTbl As Range: Set RngTbl = Me.Range("B4:" & CL(Lc) & Lr & "")
    If Application.Intersect(Target, RngTbl) Is Nothing Then
     Exit Sub ' I did not change anything in the table
    Else
     Let Application.EnableEvents = False
     Let Me.Range("A1").Value = "No Remarks"
     Let Application.EnableEvents = True
    Rem Loop to get the array
    Dim arrTbl() As Variant: Let arrTbl() = RngTbl.Value2
    Dim arrDec() As Boolean: ReDim arrDec(1 To Lr - 3)
    Dim Cnt
        For Cnt = 1 To UBound(arrTbl(), 1) ' Loop "down" "rows" in table array
        Dim Clm As Long ' "column" in table array
            For Clm = 2 To UBound(arrTbl(), 2) - 1 ' loop from second to last but one "column" in table array
                If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then
                 Let arrDec(Cnt) = True: Exit For ' we no longer have a decresing sequence
                Else
                End If
            Next Clm
        Next Cnt
    End If
' at this point I have in my  arrDec()  False  for a decreasing sequence and  True  for a non decreasing sequence
    Rem loop to build the output string
    Dim StrRemmark As String
        For Cnt = 1 To UBound(arrDec())
            If arrDec(Cnt) = False Then
             Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
            Else
            End If
        Next Cnt
' add remark
    If StrRemmark <> "" Then
     Let StrRemmark = Mid(StrRemmark, 6) ' this takes off the first  " and "
     Let Application.EnableEvents = False
     Let Me.Range("A1").Value = "Student is decreasing in " & StrRemmark
     Let Application.EnableEvents = True
    Else
    ' no remmark
    End If
End Sub
Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

After I wrote that macro above , it became obvious to me that we don’t really need that extra array, arrDec(). Instead we can directly fill in the remark string.
This next macro version below seems to work just as well, and is simpler

Public Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long, Lc As Long
 Let Lr = Me.Range("B" & Me.Rows.Count & "").End(xlUp).Row
 Let Lc = Me.Cells(4, 2).End(xlToRight).Column             '  I am using a slightly less common way including   xlToRight   because there are some explanation wordings that would be found giving a false number by the more typically used    Columns.Count xlToLeft   way
Dim RngTbl As Range: Set RngTbl = Me.Range("B4:" & CL(Lc) & Lr & "")
    If Application.Intersect(Target, RngTbl) Is Nothing Then
     Exit Sub ' I did not change anything in the table
    Else
     Let Application.EnableEvents = False
     Let Me.Range("A1").Value = "No Remarks"
     Let Application.EnableEvents = True
    Rem Loop to get the array
    Dim arrTbl() As Variant: Let arrTbl() = RngTbl.Value2
    Dim Cnt
        For Cnt = 1 To UBound(arrTbl(), 1) ' Loop "down" "rows" in table array
        Dim Clm As Long ' "column" in table array
            For Clm = 2 To UBound(arrTbl(), 2) - 1 ' loop from second to last but one "column" in table array
                If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then
                 Exit For ' we no longer have a decresing sequence
                Else
                End If
            Next Clm
            If Clm = UBound(arrTbl(), 2) Then ' this will occur if we did not exit the   For  loop
            Dim StrRemmark As String
             Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
            Else
            End If
        Next Cnt
    End If
' add remark
    If StrRemmark <> "" Then
     Let StrRemmark = Mid(StrRemmark, 6) ' this takes off the first  " and "
     Let Application.EnableEvents = False
     Let Me.Range("A1").Value = "Student is decreasing in " & StrRemmark
     Let Application.EnableEvents = True
    Else
    ' no remmark
    End If
End Sub
Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Alan

( in the uploaded workbook are two worksheets for the two macros. – (You can only have one of a particular event coding in a single worksheet) )
P.S.
If you ever want a detailed explanation of anything in my coding, then please ask.
I can’t always respond quickly – often I only pop by the forums every 1-2 days. But I prefer to explain my coding in detail to help educate, rather than continually producing similar codings. ( That is just a personal preference. Many of the best forum helpers prefer to give a lot of coding quickly, enjoying solving as many problems as quickly as possibly . -
Its nice that the World is full of lots of different people and preferences, at least I think so..... )