1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count between Two Cells

Discussion in 'Ask an Excel Question' started by fareedexcel, Oct 11, 2017 at 5:25 PM.

  1. fareedexcel

    fareedexcel New Member

    Messages:
    12
    Dear Experts,

    I require to check the count interval between the 1st OFF to the Last OFF. After the Last OFF, count should not happen. Expected result attached for your reference.

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,800
    fareedexcel
    Expected result attached for your reference.
    Just modify those '-' and 'OFF' as You need...

    Attached Files:

  3. p45cal

    p45cal Well-Known Member

    Messages:
    822
    See attached with UDF:
    Code (vb):
    Function myCounts(rng) As String
    ResultStr = ""
    rngVals = rng.Value
    For c = 2 To UBound(rngVals, 2)
      Select Case True
        Case UCase(Application.Trim(rngVals(1, c - 1))) = "OFF" And UCase(Application.Trim(rngVals(1, c))) <> "OFF": Start = c
        Case UCase(Application.Trim(rngVals(1, c - 1))) <> "OFF" And UCase(Application.Trim(rngVals(1, c))) = "OFF"
          If Start > 0 Then If Len(ResultStr) = 0 Then ResultStr = c - Start Else ResultStr = ResultStr & "-" & c - Start
      End Select
    Next c
    myCounts = ResultStr
    End Function
     

    Attached Files:

    Thomas Kuriakose likes this.
  4. fareedexcel

    fareedexcel New Member

    Messages:
    12
    Thank you for your help and support.
  5. fareedexcel

    fareedexcel New Member

    Messages:
    12
    The Code is working fine. Is there any way to understand the logic. I would like to learn more on automating stuffs in excel.
  6. p45cal

    p45cal Well-Known Member

    Messages:
    822
    I'm sorry, but no; explaining the logic in full takes 10 to 20 or even 30 times as long as writing the code.
  7. fareedexcel

    fareedexcel New Member

    Messages:
    12
    Thanks for your support Expert. I need one more modification from the same file, for eg: in the pattern if any of them has more than value 7 (6-8-6, 12-6-8,6-6-6-8) then those rows should come in another sheet. Is it possible with Macros instead of doing with formula.
  8. vletm

    vletm Well-Known Member

    Messages:
    2,800
    fareedexcel
    Yes ...
    Do You have any name of that other sheet and to which row?
    Do You have sample output?
  9. p45cal

    p45cal Well-Known Member

    Messages:
    822
    Button at cell AP1 of Check sheet in attached, which calls the code below.
    Uses the same user defined function as before.
    Code (vb):
    Sub blah()
    Limit = 7
    For Each rw In Sheets("Check").Range("I4:AM19").Rows
      x = myCounts(rw)
      If Len(x) > 0 Then
        y = Split(x, "-")
        GreaterThanLimit = False
        For Each Z In y
          If CLng(Z) > Limit Then
            GreaterThanLimit = True
            Exit For
          End If
        Next Z
        If GreaterThanLimit Then Set Destn = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1) Else Set Destn = rw.Cells(1).Offset(, 33)
        With Destn
          .NumberFormat = "@"
          .Value = x
        End With
      End If
    Next rw
    End Sub
     

    Attached Files:

    Last edited: Oct 13, 2017 at 10:08 AM
  10. fareedexcel

    fareedexcel New Member

    Messages:
    12
    After the pattern is captured in the 1st sheet. Above 7 should get captured in the second sheet. Please help on the same. Expected result attached. Thanks for the help Experts.
  11. fareedexcel

    fareedexcel New Member

    Messages:
    12
    Output I have mentioned right now
  12. vletm

    vletm Well-Known Member

    Messages:
    2,800
    fareedexcel
    Output as You have mentioned right now...

    Attached Files:

    fareedexcel and Thomas Kuriakose like this.
  13. p45cal

    p45cal Well-Known Member

    Messages:
    822
    See attached.

    Attached Files:

    fareedexcel and Thomas Kuriakose like this.
  14. fareedexcel

    fareedexcel New Member

    Messages:
    12
    This is Brilliant. Thanks for the Support
  15. fareedexcel

    fareedexcel New Member

    Messages:
    12
    Thanks Expert :)

Share This Page