Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: How to get the how many times this come in this sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,960
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to get the how many times this come in this sheet

    with PowerQuery

    S.no. Marks Yes/no CR Count
    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:
    // 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
    extended example:

    S.no. Marks Yes/no CR Count
    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 by sandy666; May 19th, 2019 at 05:41 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,653
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    10 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    Results start "D1".
    Code:
    Sub MG19May12
    Dim Rng As Range, Dn As Range, c As Long, b As Long, nRng As Range, oMax As Long
    Set Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    For Each Dn In Rng
    If Dn.Value = "yes" Then
        If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
    End If
    Next Dn
    
    For Each Dn In nRng.Areas
        If Dn.Count = 2 Then c = c + 1
        If Dn.Count = 3 Then b = b + 1
        oMax = Application.Max(oMax, Dn.Count)
    Next Dn
    
    [D1] = """Yes"" 2 Times": [E1] = c
    [D2] = """Yes"" 3 Times": [E2] = b
    [D3] = "Max  ""Yes"" Times": [e3] = oMax
    End Sub
    Regards Mick

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,551
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    31 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    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:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    thanks bro,

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    Thanks bro.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    1,960
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    Post your Excel version (PC / Mac?), also link to shared excel file with representative example of source data and expected result.
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    Hi Mick, Showing the following error

    Run-time error '91':

    Object variable or with block variable not set

    help pls

  9. #9
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    help pls bro................waiting for reply

  10. #10
    New Member
    Join Date
    Mar 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to get the how many times this come in this sheet

    i have tried it through vba but not working, help pls

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •