Consulting

Results 1 to 15 of 15

Thread: reports the distance between values with respect to a given value

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    95
    Location

    reports the distance between values with respect to a given value

    I ask for help with the following :


    I would like the distance (number of rows) between the values in column I that are greater than or equal to the value entered in cell D1 to be returned in column B,

    I'm working on excel 2021

    thanks in advance for the support
    Attached Files Attached Files

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    847
    Location
    This seems to work. HTH. Dave
    Sub test()
    Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
    End With
    For Cnt = 1 To LastRow
    Tcnt = Tcnt + 1
    If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
    ws.Cells(Cnt, "B") = Tcnt
    Tcnt = 0
    End If
    Next Cnt
    End Sub

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    847
    Location
    It would probably be better to clear the "B" column for a reset. Dave
    Sub Test2()
    Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
    .Range(.Cells(1, 2), .Cells(LastRow, 2)).ClearContents
    End With
    For Cnt = 1 To LastRow
    Tcnt = Tcnt + 1
    If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
    ws.Cells(Cnt, "B") = Tcnt
    Tcnt = 0
    End If
    Next Cnt
    End Sub

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Don't know Dave. Even after correcting the sheet name, it adds the row counts.

    Sorry Dave, when I posted only your first post was there.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    847
    Location
    Hmmm.... not real sure what you mean @Aussiebear. Isn't that what iwas supposed to happen? Total number of rows in "I" that are >= D1 go to "B" row where the "I" value is found. Total resets after an "I" value is found. Maybe I misunderstood but my limited testing seem to work. Dave

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    I was under the impression that the OP wanted to count the rows between the specific values where ever they were found in the column I
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,910
    In the attached a formula in cell O4, with hints for arguments:

    2024-05-29_155905.jpg

    This (in Excel 2021) is based on a named lambda formula. The name is Distances (see Name Manager in the Formulas tab of the ribbon), its formula is:
    =LAMBDA(rng,LowerLimit,LET(a,FILTER(SEQUENCE(ROWS(rng)),rng>=LowerLimit),b,DROP(VSTACK({0},a),-1),a-b-1))
    If you don't want to create a named formula you could use this formula directly on the sheet (cell M4) which is:
    =LAMBDA(rng,LowerLimit,LET(a,FILTER(SEQUENCE(ROWS(rng)),rng>=LowerLimit),b,DROP(VSTACK({0},a),-1),a-b-1))(I1:I53,D1)
    I know it's not in the same format that you asked for but could it be of any use?
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular
    Joined
    Jan 2015
    Posts
    95
    Location
    Good morning and thanks to everyone


    Dave's routine does what I wanted. I only notice that the returned count is not exactly the distance between one occurrence and the next - see photo with the value 20 placed in D1 -


    thanks again for your support
    count.jpg

  9. #9
    VBAX Regular
    Joined
    Jan 2015
    Posts
    95
    Location
    .. with

    ws.Cells(Cnt, "B") = (Tcnt-1)

    now returns the desired values

    thanks again everyone

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Why does Tcnt -1 need brackets?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    My concern here is that if we use Dave's second submitted code which the OP say works if we change Tcnt to (tcnt-1) in line 11.

    Sub Test2()
        Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
        Set ws = ThisWorkbook.Sheets("Sheet1")
        With ws
            LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
            .Range(.Cells(1, 2), .Cells(LastRow, 2)).ClearContents
        End With
        For Cnt = 1 To LastRow
            Tcnt = Tcnt + 1
            If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
               ws.Cells(Cnt, "B") = Tcnt
               Tcnt = 0
           End If
        Next Cnt 
    End Sub
    In this code we are setting Tcnt to 1
    Tcnt = Tcnt + 1
    Why isn't Tcnt set to zero initially? If we follow the OP's suggestion of amending the following line to this
    ws.Cells(Cnt, "B") = (Tcnt - 1)
    You are setting the value back to Zero if it finds a value greater than or equal to the value in cell D1, which means it doesn't count the find.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  12. #12
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    847
    Location
    Hi R161. Looking at your output, by my count, it seems like I was right. I1:14 gives you 4. I5 gives you 1. Nine rows add the row with the number that you were looking for gives you 10. 3 rows add the row that you were looking for gives you 4. Not sure why why tcnt -1 is needed? Dave

  13. #13
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Dave, it seems the OP wanted the gap between the values so the results should be 3,10,9,3,2,12
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  14. #14
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    847
    Location
    See that's what puzzling. U misspelled the OP wanted 3,9,9,3,2,12. 10 was bad. Not sure about the 12... can't see that many rows. With the OP's rules as you suggest, it should be 3,9,9,3,1,1,2(that I can see). Tcnt can't be set to zero initially because there's always one row. As long as everybody's happy. Dave

  15. #15
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    I was using the workbook provided in Post#1
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Tags for this Thread

Posting Permissions

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