How can i count a continuous text in a data series...

[amit_k21000]

Guyz ...Lets say i have a data in 50 fields...The data is only in form of TRUE and FALSE

Now , in next field i want as an output that say "YES" if there is a continuous occurrence of TRUE for 9 cells or more.

Ex:

FALSE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[MickG]

Try this for data starting "A2"

[COLOR="Navy"]Sub[/COLOR] MG15Sep01
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] R.Count >= 9 [COLOR="Navy"]Then[/COLOR] R.Offset(, 1) = True
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[amit_k21000]

Thanks a lot Mick for prompt revert... But how to use it in excel as its not a regular formula ?

Sorry...If my que is sillly as i am not great in excel..

Try this for data starting "A2"

[COLOR=Navy]Sub[/COLOR] MG15Sep01
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, nRng [COLOR=Navy]As[/COLOR] Range, R [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn.Value [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] nRng [COLOR=Navy]Is[/COLOR] Nothing [COLOR=Navy]Then[/COLOR] [COLOR=Navy]Set[/COLOR] nRng = Dn Else [COLOR=Navy]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] R [COLOR=Navy]In[/COLOR] nRng.Areas
    [COLOR=Navy]If[/COLOR] R.Count >= 9 [COLOR=Navy]Then[/COLOR] R.Offset(, 1) = True
[COLOR=Navy]Next[/COLOR] R
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have Excel through Office 365, try something like this.

Excel Workbook
	A	B
1
2	FALSE	Yes
3	FALSE
4	TRUE
5	TRUE
6	TRUE
7	TRUE
8	TRUE
9	TRUE
10	TRUE
11	TRUE
12	TRUE
13	FALSE
TRUE x 9

 

[amit_k21000]

Thanks..but sorry not working...Also it should yes on B12 in the screenshot why it saying yes on B2

Welcome to the MrExcel board!

If you have Excel through Office 365, try something like this.

TRUE x 9

	A	B
1
2	FALSE	Yes
3	FALSE
4	TRUE
5	TRUE
6	TRUE
7	TRUE
8	TRUE
9	TRUE
10	TRUE
11	TRUE
12	TRUE
13	FALSE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"><col style="width:42px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IFERROR(IF(FIND(REPT("TRUE",9),CONCAT(A2:A13)),"Yes",""),"No") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Fluff]

How about

Excel 2013 32 bit
	A	B
1	FALSE
2	FALSE
3	TRUE	Yes
4	TRUE
5	TRUE
6	TRUE
7	TRUE
8	TRUE
9	TRUE
10	TRUE
11	TRUE
12	FALSE
Sheet4

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(A1:A9,TRUE)=9,"Yes","")

 

[amit_k21000]

Thanks..Its working but not properly... I mean it is saying Yes but in all the lines... I want yes at the end of 9 consecutive TRUE only.. so like in cell B11 only in the table.

FALSE	Yes
FALSE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
TRUE	Yes
FALSE	Yes

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

How about

Excel 2013 32 bit

	A	B
1	FALSE
2	FALSE
3	TRUE	Yes
4	TRUE
5	TRUE
6	TRUE
7	TRUE
8	TRUE
9	TRUE
10	TRUE
11	TRUE
12	FALSE

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =IF([COLOR=rgb(255]COUNTIF(A1:A9,TRUE)=9,"Yes",""[/COLOR]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Fluff]

Please do not quote entire posts as it is unnecessary and make sit harder to follow the thread.

Where is your data & what formula did you use?

 

[Rick Rothstein]

Thanks..Its working but not properly... I mean it is saying Yes but in all the lines... I want yes at the end of 9 consecutive TRUE only.. so like in cell B11 only in the table.

If your data starts in cell A1, then put Fluff's formula in cell B9 and copy it down from there to the end of your data. If, on the other hand, your data starts in cell A2, then use this modification of Fluff's formula which accounts for the range change...

=IF(COUNTIF(A2:A10,TRUE)=9,"Yes","")

and place it in cell B10.

 

[amit_k21000]

Sorry..Will keep in mind going forward...

Please do not quote entire posts as it is unnecessary and make sit harder to follow the thread.

Where is your data & what formula did you use?