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

A

amit_k21000

New Member
Joined
Sep 15, 2018
Messages
6
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:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try this for data starting "A2"
Code: 
[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
 
Upvote 0
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..:(

MickG said:
Try this for data starting "A2"
Code: 
[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
Click to expand...
 
Upvote 0
Welcome to the MrExcel board!

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

Excel Workbook
AB
1
2FALSEYes
3FALSE
4TRUE
5TRUE
6TRUE
7TRUE
8TRUE
9TRUE
10TRUE
11TRUE
12TRUE
13FALSE
TRUE x 9
 
Upvote 0
Thanks..but sorry not working...Also it should yes on B12 in the screenshot why it saying yes on B2

Peter_SSs said:
Welcome to the MrExcel board!

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

TRUE x 9

AB
Yes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"><col style="width:42px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...
 
Upvote 0
How about


Excel 2013 32 bit
AB
1FALSE 
2FALSE
3TRUEYes
4TRUE
5TRUE
6TRUE
7TRUE
8TRUE
9TRUE
10TRUE
11TRUE
12FALSE
Sheet4
Cell Formulas
RangeFormula
B1=IF(COUNTIF(A1:A9,TRUE)=9,"Yes","")
 
Upvote 0
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.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"]FALSE[/TD]
[TD="class: xl63, width: 64"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]TRUE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63"]Yes[/TD]
[/TR]
</tbody>[/TABLE]


Fluff said:
How about

Excel 2013 32 bit
AB
Yes

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]B1[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]COUNTIF(A1:A9,TRUE)=9,"Yes",""[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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?
 
Upvote 0
amit_k21000 said:
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.
Click to expand...
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
Rhodie72
  • Question Question
How to write a data to a text file (VBA)
2
Replies
17
Views
1K
Rhodie72
Rhodie72
H
  • Question Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
566
GraH
GraH
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top