How to get the how many times this come in this sheet

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
346
Office Version
2010, 2007
Platform
Windows, Web
Thanks in advance,

I have following sheet
S.no. Marks Yes/no
1. 13. No
2. 16. yes
3. 18. yes
4. 19. No
5. 22 yes
6. 24. yes
7. 26. yes
8. 33. No
9. 35. No
10. 12. yes
11. 17. No
12. 38. yes
13. 40. yes
14. 42. yes
15. 44. yes
16. 46. yes

My query is I want to know how many times

1. 'continuesly three time yes' in third column - come in the above sheet

2. 'continuesly two time yes' in third column - come in the above sheet

3. 'Maximum time continues yes' in third column - comes in the above sheet

And is
1. Three time yes comes only one time
2. Two time yes comes only one time
3. Maximum time continues yes comes is 5

Please provide the VBA code or any formula for the above three point
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,426
with PowerQuery

S.no.MarksYes/noCRCount
1​
13​
No
1​
1​
2​
16​
Yes
2​
1​
3​
18​
Yes
3​
1​
4​
19​
No
5​
1​
5​
22​
Yes
6​
24​
Yes
7​
26​
Yes
8​
33​
No
9​
35​
No
10​
12​
Yes
11​
17​
No
12​
38​
Yes
13​
40​
Yes
14​
42​
Yes
15​
44​
Yes
16​
46​
Yes

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp1 = Table.Group(Source, {"Yes/no"}, {{"Count", each _, type table}, {"CR", each Table.RowCount(_), type number}},GroupKind.Local),
    Filter = Table.SelectRows(Grp1, each ([#"Yes/no"] = "Yes")),
    Grp2 = Table.Group(Filter, {"CR"}, {{"Count", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Grp2,{{"CR", Order.Ascending}})
in
    Sort[/SIZE]
extended example:

S.no.MarksYes/noCRCount
1​
13​
No
1​
1​
2​
16​
Yes
2​
2​
3​
18​
Yes
3​
1​
4​
19​
No
5​
1​
5​
22​
Yes
6​
24​
Yes
7​
26​
Yes
8​
33​
No
9​
35​
No
10​
12​
Yes
11​
17​
No
12​
38​
Yes
13​
40​
Yes
14​
42​
Yes
15​
44​
Yes
16​
46​
Yes
17​
47​
No
18​
48​
Yes
19​
49​
Yes
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Results start "D1".
Code:
[COLOR="Navy"]Sub[/COLOR] MG19May12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Value = "yes" [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] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count = 2 [COLOR="Navy"]Then[/COLOR] c = c + 1
    [COLOR="Navy"]If[/COLOR] Dn.Count = 3 [COLOR="Navy"]Then[/COLOR] b = b + 1
    oMax = Application.Max(oMax, Dn.Count)
[COLOR="Navy"]Next[/COLOR] Dn

[D1] = """Yes"" 2 Times": [E1] = c
[D2] = """Yes"" 3 Times": [E2] = b
[D3] = "Max  ""Yes"" Times": [e3] = oMax
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,937
Office Version
2010
Platform
Windows
Here is a function (can be called from other VBA code or can be used directly within an Excel formula) that takes one required argument (the vertical range of cells containing the Yes/No values) and one optional argument (if you specify a positive number, it will return the count of that many consecutive yesses within the range and, if omitted or if a zero is passed into the second argument, it will return the maximum count for consecutive yesses within the range)...
Code:
[table="width: 500"]
[tr]
	[td]Function Yesses(VertRng As Range, Optional Count As Long) As Long
  Dim Combined As String, V As Variant
  Combined = "NO" & Replace(UCase(Join(Application.Transpose(VertRng), "")), " ", "") & "NO"
  If Count Then
    Yesses = UBound(Split(Combined, "NO" & Application.Rept("YES", Count) & "NO"))
  Else
    For Each V In Split(Application.Trim(Replace(Combined, "NO", " ")))
      If Len(V) / 3 > Yesses Then Yesses = Len(V) / 3
    Next
  End If
End Function[/td]
[/tr]
[/table]
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
346
Office Version
2010, 2007
Platform
Windows, Web
thanks bro,
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,426
You are welcome

Have a nice day
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
346
Office Version
2010, 2007
Platform
Windows, Web
Hi Mick, Showing the following error

Run-time error '91':

Object variable or with block variable not set

help pls
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
346
Office Version
2010, 2007
Platform
Windows, Web
help pls bro................waiting for reply
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
346
Office Version
2010, 2007
Platform
Windows, Web
i have tried it through vba but not working, help pls
 

Watch MrExcel Video

Forum statistics

Threads
1,099,677
Messages
5,470,083
Members
406,680
Latest member
Cleudson Dias de Almeida

This Week's Hot Topics

Top