red flag

[khalil]

hello


how to red flag when 9 consecutive entries are the same in column A, there are some blank cells in between.
we can use A1 as fixed cell to show red, and the entries will be in A2,A3....A100.

thanks

 

[khalil]

good morning

can we modify it little bit

like,
if there is a word entree the number in cell B1 = 0, then when ,solo, it starts to count 1,2,3,....9 (a series of solo) , then when word entree, again B1 =0,
then when solo it starts to count again 1,2,3...9

in that sequence

thanks

 

[ZVI]

Khalil, it’s not clear for me.
Could you please post cells data layout and the required result?

 

[khalil]

hello my friend

basically what i want is Cell A1 to count how many consecutive solo entries in column A then when any dual entree in the same column then cell A1 =0 in order to start counting another solo entries again after the dual entree.
each time there is dual entry A1 =0
any solo entry after dual will be counted in A1
then when there is another dual entry A1 = 0 again,
i mean by dual entree is the word dual in the cell
& so on....

the range in the same column to go as far as 1000 entries.or more


this example is for column A, the same with another 50 columns
considering each column for a student,
my project is all set waiting for this cod


thanks
khalil

 

[ZVI]

Khalil, please post two examples of the data in the cells.
Not explanation, but just data and the required result in A1.
First example is for A1=0, the second one is for A1=3

 

[khalil]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 8pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">FALSE</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">word</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt">text</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 10pt" rowSpan=11>9 in series</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-SIZE: 10pt">SOLO</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 10pt">Solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-SIZE: 10pt">word</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="FONT-SIZE: 10pt">text</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE>
hello my friend, thanks again

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 8pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">TRUE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff99; FONT-SIZE: 10pt; FONT-WEIGHT: bold">FALSE</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 10pt">word</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 10pt">text</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 10pt" rowSpan=11>9 in series</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-SIZE: 10pt">SOLO</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 10pt">Solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 10pt">solo</TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-SIZE: 10pt">word</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="FONT-SIZE: 10pt">text</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE> A A


1 3 1 0


2 Dual 2 Solo

3 Solo 3 solo

4 4 solo

5 Solo 5 dual

6 Solo

 

[khalil]

sorry sent by mistake

 

[khalil]

sorry i am having hard time posting example
i dont how to use these format

 

[ZVI]

Khalil, here is the instruction:
1. Open this page in Internet Explored (don't use an alternative explorer).
2. Press Post Replay button.
3. In Excel copy range of the example Sheet.
4. Paste it to the replay message window.
5. Press Submit replay button.

 

[ZVI]

Ok, may be this:

' Max length of chain in series after ResetVal with skipping of empty values
' Cell formula: =MaxLengh(A2:A100,"dual")
' VBA call    :  MsgBox MaxLengh(Range("A2:A100"),"dual")
Function MaxLengh(Series, ResetVal) As Long
  Dim arr, old, v$, x, y&
  arr = Series
  For Each x In arr
    v = Trim(x)
    If Len(v) > 0 Then
      If StrComp(v, ResetVal, vbTextCompare) = 0 Then
        y = 0
        MaxLengh = 0
        old = Empty
      ElseIf StrComp(v, old, vbTextCompare) = 0 Then
        y = y + 1
        If y > MaxLengh Then MaxLengh = y
        If StrComp(v, ResetVal, vbTextCompare) = 0 Then y = 0: MaxLengh = 0
      Else
        y = 1
        old = v
      End If
    End If
  Next
End Function

Excel Workbook
	A	B	C
1	3	0	1
2			solo
3	Dual	solo
4	Solo	solo	solo
5		solo	solo
6	Solo	dual	dual
7	solo		solo
8
Sheet

 

[khalil]

good morning my friend

yes thats what i was trying to put in example,but

all three options should be in one column,
i need a range of columns, because each column will represent a student

at least 50 students(50 columns).


thanks