Showing Custom Value Based on the Condition of Dynamic Table

**Anshu** · 09-11-2020, 12:36 PM

Hello!

I have a table B4:G4. (simplified here, actual table is long enough having more than 80 cells)
Table.jpg

B4, B5 and B6 are linked to another cell. So their value may change depending on the value of the linked cell. (therefore, I used the term 'dynamic table')

There is a merged cell range A1:C1, where the remarks would be appear.

Now, my requirement is...

1. If value of C4 > D4 > E4 > F4 > G4, (descending order), then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4)"
For example, "Student is decreasing in English" ( Here the the cell value "English" may change, so I used "(Value of Cell B4, instead of the current value, which is English)

2. Similarly, If value of C6 > D6 > E6 > F6 > G6, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B6)"
For example, "Student is decreasing in Maths"

3. Similarly, If value of C5 > D5 > E5 > F5 > G5, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B5)"
For example, "Student is decreasing in Hindi"

(It can be noticed that points 1, 2 and 3 are nearly same)

4. If both the point 1 and 2 are true, that is, value of C4 > D4 > E4 > F4 > G4 and value of C6 > D6 > E6 > F6 > G6, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4) and (Value of B6)"
For example, "Student is decreasing in English and Maths"

5. If all the point 1 and 2 and 3 are true, that is, value of C4 > D4 > E4 > F4 > G4, value of C6 > D6 > E6 > F6 > G6 and value of C5>D5>E5>F5>G5, then the merged cell A1 will show the value " Student is decreasing in (Value of Cell B4), (Value of B6) and (Value of B5)"
For example, "Student is decreasing in English, Maths and Hindi"

(Again, point 4 and 5 are nearly same)

6. If all of the points are false, that is, there is no any decreasing sequence (as shown in range C5:G5), then the merged cell A1 will show the value "No Remarks"

Important:
All of the above conditions are applied only if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied all the three condition, i.e, they are in descending order, and they are consecutive (side by side), and they are minimum three.

(Please find the attachment below to understand the whole picture clearly)

Thanks in advance!

**DocAElstein** · 09-13-2020, 01:46 PM

Hi,
This is probably a question that is half a VBA question and half a bit of logical thinking.
I don’t think it is difficult, but just requires a bit of tedious coding.

I will give you some ideas, first. Tomorrow, if you still need help, I will give you a solution, based on the logic below:

My initial thinkings
My logical thinking is not necessarily the best. I am sure if you think about this problem long enough then you can come up with lots of different ways to do it .

My initial thinking is that you build up an array ( list ) of flags for each row. Anything will do, like for example a 1 could indicate that student was decreasing in that row .
Then you would loop through that array ( list ) , and build up the string to be pasted out.

To explain that pictorially : I am talking about producing something like this shown in yellow:

ENGLISH	50	45	30	20	15	1
HINDI	45	60	40	50	65	0
MATHS	70	55	40	25	10	1

That yellow column would not necessarily be in a spreadsheet. It could be built up in an array in the coding, ( pseudo like: my1sArr() = {1,0,1} ) , and that array then used in a loop internally to build up the string to be pasted out to A1.
That is just my initial thinking.
Producing an array or list of those 1’s can easily be done with some VBA coding.
Routinely when I would do such coding, that list would be produced in a way that it does not rely on a fixed table size. It is typical in such coding to calculate first the current Last row and last column. ( Typically in my codings I use the Long type variables, Lr and Lc

You want all this to happen automatically I assume, so you would need to have the coding in the worksheet event macro , Private Sub Worksheet_Change(ByVal Target As Range)
The initial code lines would typically be those to restrict the macro to running when you select within the table range. The very first code lines would need to determine the table range based on top left of B4 and bottom right based on a on determining Lr and Lc

I expect also that your problem could be solved by a clever formula. I have seen some very clever formula solutions at excelforum.com and mrexcel.com and eileenslounge.com to solve similar problems to yours. But I have no experience with formulas.

I will first have time again tomorrow to help further. If you do not get a solution in the meantime, then I will give you a macro to match the logic I described above

Alan

P.S.
If you want to make a start on a macro following my logic, then the first few code lines would be those required to calculate Lr and Lc
Those are the ones that typically take a form , pseudo like similar to
Lr = _.Range ( _ , _.Rows.Count ) .End(xlup) .Row
Lc = _.Cells ( _ , _.Column.Clount ) .End(xlToLeft) .Column

The next bit is to restrict the coding to work on the table range

**Anshu** · 09-14-2020, 12:51 PM

Thank you for the help and guidance!

I'm too new for VBA to create a long macro independently, and this is my struggle.

However, excel formula seems easier for me than VBA. Hence, as per your suggestion, I've tried to solve my problem with excel formulae (by trial and error method). And it tooks nearly 3 hours of time to frame this formula which works fine and satisfies all the points I've mentioned in the problem...

Code:

=IFS(AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B4&", "&B5&" & "&B6,AND(OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B5&" & "&B6,AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6))),"Student is decreasing in "&B4&" & "&B6,AND(OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5))),"Student is decreasing in "&B4&" & "&B5,OR(AND(C4>D4,D4>E4),AND(D4>E4,E4>F4),AND(E4>F4,F4>G4)),"Student is decreasing in "&B4,OR(AND(C5>D5,D5>E5),AND(D5>E5,E5>F5),AND(E5>F5,F5>G5)),"Student is decreasing in "&B5,OR(AND(C6>D6,D6>E6),AND(D6>E6,E6>F6),AND(E6>F6,F6>G6)),"Student is decreasing in "&B6,TRUE,"")

The only issue is the length of the formula. As I've mentioned already that the actual table I have is made up of 10 Columns and 8 Rows (10x8). Only 3x5 cells require such a long formula, then what will be case if I'm going to frame the formula for 10*8 cells!! Because, with the increase of each new row, the formula is increasing like squre functions (exponentially). I doubt, the formula may cross the limit of excel formula length!

So, my question is, should I proceed with the formula, or I should see for VBA for solution??

**DocAElstein** · 09-14-2020, 02:09 PM

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 }

Code:

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

Code:

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..... )

**Anshu** · 09-14-2020, 09:56 PM

Thank you for the Macro. It works fine!
But there are 2 important points which I want to mention...

Point 1) Missing comma: When all the three rows contains values in descending order, then B4 shows - Student is decreasing in ENGLISH and HINDI and MATHS
It should be - Student is decreasing in ENGLISH, HINDI and MATHS (as we normally write in English language)

Point 2) I've forgot one point to mention in my top post. So I edited my top most post after 3 hours of first posting. So, I think, the point got unnoticed. It's here...

Important:
All of the above conditions are applied only if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied all the three condition, i.e, they are in descending order, and they are consecutive (side by side), and they are minimum three.

In the support of the line, I've edited the attachments too.
__________________________________________________ __________________________________________________ _______________________________________

If you ever want a detailed explanation of anything in my coding, then please ask.
I prefer to explain my coding in detail to help educate, rather than continually producing similar codings

How nice your idea is! I really like it.
But before asking for any explanation, I first need to understand at least 70 to 80 percent of the macro provided above. And for understanding this highly compex and complicated coding (for me) for a person like me, who has just started his journey of excel 3 months ago, it may took several days. First, I have to search the meaning of various terms used in the code above like .End(xlUp).Row, UBound etc. Then I have to search for the meaning of various lines used in the code above. Only then I'll consider myself eligible to ask for any explaination here.
Thank you very much for educating and improving my area of knowledge, surprisingly, without any cost!

**DocAElstein** · 09-15-2020, 02:58 PM

Originally Posted by Anshu

Point 2)..... I think, the point got unnoticed. It's here...
… All of the above conditions are applied if there are minimum 3 consecutive cells which are in descending order.
For example, cells D5, E5 and F5 have values which are satisfied both the condition, i.e, they are in descending order, and they are consecutive (side by side)…

Yes , I think I missed that.
But its quite easy to do. As previously there are probably lots of ways to do it because
_ there are lots of logics to achieve the same
_ for each logic there are usually many different ways to apply it in VBA
The following macro change is the first thing I thought of, so it is unlikely to be the best

We change the inner loop that is looping through the columns,

Code:

        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

This alternative below will keep looping along the columns until we either
have 3 consecutive cells which are in descending order,
or until we have checked all columns

Code:

        Dim Clm As Long: Let Clm = 2 ' "column" in table array
        Dim Decs As Long
            'For Clm = 2 To UBound(arrTbl(), 2) - 1 ' loop from second to last but one "column" in table array
            Do
                If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then ' we no longer have a decresing sequence
                 Let Decs = 0 ' Reset the count of sequential decreasing values
                Else ' we have at least 2 sequential decreases, possibly 3
                 Let Decs = Decs + 1
                End If
            'Next Clm
             Let Clm = Clm + 1
            Loop While Clm < UBound(arrTbl(), 2) And Decs < 2
            'If Clm = UBound(arrTbl(), 2) Then ' this will occur if we did not exit the   For  loop
            If Decs = 2 Then ' If decs = 2 we had three seqeuntial decreses = sequentially 2 x arrTbl(Cnt, Clm + 1) < arrTbl(Cnt, Clm)
            Dim StrRemmark As String
             Let StrRemmark = StrRemmark & " and " & arrTbl(Cnt, 1)
             'Let StrRemmark = StrRemmark & ", " & arrTbl(Cnt, 1)
            Else
            End If
         Let Decs = 0 ' reset the count of sequential decreasing values so that  Decs  can be used in the next main row loop

Here is a full macro including that inner loop change: https://excelfox.com/forum/showthrea...ll=1#post14915

Point 1) Missing comma : When all the three rows contains values in descending order, then B4 shows - Student is decreasing in ENGLISH and HINDI and MATHS
It should be - Student is decreasing in ENGLISH, HINDI and MATHS (as we normally write in English language)

In VBA there are lots of useful sting manipulation functions and statements. ( I guess this all comes from the earlier Visual Basic ( VB ) language, and/or the fact that all software is just long strings of text, so string manipulation is fundamental to any computer software…. I would advise learning about the various string functions at an early stage of learning VBA https://docs.microsoft.com/en-us/dot...ring-functions )
Once again , we can think of many different ways to change the final string format

My first thought is to replace the
“ and “
With
“, “

Then , if we have at least one “, “ in the final string, then we will replace that with “ and “

There is an extra change that you may be interested in: We can easily change something like
MATHS
to
Maths
One way to do that is to replace a code line like
arrTbl(Cnt, 1)
to
Left(arrTbl(Cnt, 1), 1) & Mid(LCase(arrTbl(Cnt, 1)), 2)
That change effectively changes a string like
MATHS
To
M & aths = Maths

Here is the next macro version: https://excelfox.com/forum/showthrea...ll=1#post14916
That event macro is also in the first worksheet of the uploaded file ( Share link below )

Alan

Share link 'Dynamic Table.xls' : https://app.box.com/s/366xsar7yt28pfsserl84mopfczoyow9

**Anshu** · 09-15-2020, 04:52 PM

GREAT!!
The macro is working just awesome!

THANKS A LOT!!!

I would advise learning about the various string functions at an early stage of learning VBA https://docs.microsoft.com/en-us/dot...ring-functions )

Thank you for this link. I've added it to my bookmarks and will try to learn them as soon as possible.

We can easily change something like
MATHS
to
Maths

I really like and appreciate this change.

And I'm thankful for all these efforts, macros, help and guidance!!

**Anshu** · 09-15-2020, 05:25 PM

While working with the code, one small problem is detected, which is big for me, because I find myself unable to solve it.

When all the cells of the table (C4:G6) are empty, and I put any value in the first cell of the table (C4), it gives the following error...

Runtime error '9' : Subscript out of range

Debug.jpg

On clicking on "Debug" option following line is highlighted in VBA

Code:

If arrTbl(Cnt, Clm + 1) >= arrTbl(Cnt, Clm) Then ' we no longer have a decresing sequence

Error 9.jpg

**DocAElstein** · 09-15-2020, 07:06 PM

Hi

The macro is based on a simple regular ( not jagged) 2 Dimensional range. In other words it is based on all rows having the same number of columns.
Any other situation is likely to cause problems. That problem that you have encountered may be the frist of many similar problems. Just solving that one problem won’t get us very far, as then tomorrow, or the day after, you will have another problem and so on, and so on…. A lot of time may be wasted writing macros that we then don’t need.

There are two things you need to do consider…
_1) Think very carefully of all the situations that may occur: Think of all the situations that might occur other than those resulting in a simple regular ( not jagged) 2 Dimensional range
_1b) Consider if the macro should only start when a regular ( not jagged) 2 Dimensional range , for example of minimum 2 columns of data occurs.
Will there likely be different numbers of columns?
Could there be empty columns within a row? – If so, how should that situation be handled??
Etc… etc…
_2) Think about whether an event macro ( one that starts automatically ) is the best for this situation. ( Event macros are actually quite advanced. When learning VBA one typically first meets event macros at a middle to advanced level )
Another possibility to consider is if it might be better to just have a normal macro. I mean by this a macro that does not start automatically. You would decide when to run the macro, for example via a button

I will have time to look again tomorrow. In the meantime, the more you can think of exactly what you want, and what situations might occur, the better chance we will have of getting it right without too many re writing of coding.

( A last thing to bear in mind. As the code given to you in a help forum becomes more complicated, it will be even more important for you to get to grips with learning and understanding the coding. In the future you wont have the support you might expect from having had more professional help..
Software is not as exact a science, as it probably should be, especially if Microsoft had anything to do with it!
The more complicated a coding is, the more chances are that problems occur in the future , even if you think nothing has changed. This is especially true if you choose to keep your Microsoft software up to date. Microsoft seem to be getting more and more incompetent and changes / updates seem to cause an increasing number of problems.
In short, it is getting more and more difficult to be just a user of software. To be a user, you need also to have some idea about the workings of any software you use… It probably should not be like that, but unfortunately it is… and increasingly so.
I am a builder of houses mostly these days. A manual labourer. . Previously I studied Physics at an advanced level, but I had no interest in computing. I needed Excel VBA for an important personal project a few years ago. I found, to my surprise, that it was not possible to use it effectively without getting quite knowledgeable about it.
I don’t understand why it is like that. One possibility is that most professional programmers are idiots, or they prefer to get things wrong, so that they will always be needed to repair the mess they make… )

Alan

**Anshu** · 09-16-2020, 06:29 AM

Hi!
I've corrected the problem by simply adding a simple error handling line "On Error Resume Next", and now it works fine.

Thank you for the suggestions! I'll keep remember those points.