Look up values in Excel table, and return row reference.

Posted on 2009-12-28
Last Modified: 2012-05-08

I have several worksheets in excel, and what I am looking at is how long the data in in one column stays below a certain value. The attached example shows an excerpt of data. Column A is time, column F is variable I want to measure (how long it stays below a certain value ($L$1)). I can find the value where it crosses (Col I: "begin", "end") but I cannot figure how to automatically count / look up time between "begin/ "end" (at the bottom of the file I copied filtered data to show what time interval it "starts", "ends".) I would prefer a formula if possible; I am not so familiar with VBA. Ultimately, I wanted to have rows at top of sheet with delta time(s) (similar to what it is sheet 2)


Question by:pweber_ep
    LVL 18

    Expert Comment


    Use advanced filter.

    K1 must be empty.In K2 on Accel,


    Define names


    Refers to: =Accel!$A$1:INDEX(Accel!$I$1:$I$65536,MATCH(9.999999E+307,Accel!$A$1:$A$65536))


    Refers to: =Accel!$K$1:$K$2

    Now select sheet2

    goto Data > Advanced filter

    See image for rest.


    Author Comment

    Thanks. This is somewhat quicker than the multi-stage filtering I was doing.
    However, I was still looking for a way to automatically count and update if I change the limit ($L$1). Is this possible?
    LVL 18

    Accepted Solution


    leave the criteria range as it is(with the formula)

    Copy the following code

    Right click on tab Sheet2 > View code > paste the code there

    Private Sub Worksheet_Activate()
    Dim SourceSht   As Worksheet
    Dim SourceRng   As Range
    Dim CriteriaRng As Range
    Dim Dest        As Range
    Dim LastRow     As Long, FinalRow As Long
    Set SourceSht = Sheets("Accel")
    LastRow = SourceSht.Range("a" & Rows.Count).End(xlUp).Row
    Set SourceRng = SourceSht.Range("a1:i" & LastRow)
    Set CriteriaRng = SourceSht.Range("k1:k2")
    Set Dest = ActiveSheet.Range("a1")
    SourceRng.AdvancedFilter xlFilterCopy, CriteriaRng, Dest, 0
    FinalRow = Range("a" & Rows.Count).End(xlUp).Row
    With Range("j2")
        .Offset(-1) = "Time Under"
        .Offset(1).FormulaR1C1 = "=rc[-9]-r[-1]c[-9]"
        .Resize(2).AutoFill Destination:=.Resize(FinalRow - 1), Type:=xlFillDefault
        .Offset(-1, 2) = "Number of times under:"
        .Offset(-1, 4).Formula = "=countif(i2:i" & FinalRow & ",""begin"")"
        .Offset(, 2) = "Average time under:"
        .Offset(, 4).Formula = "=average(j2:j" & FinalRow & ")"
        .Offset(1, 2) = "Max time under:"
        .Offset(1, 4).Formula = "=MAX(J2:J" & FinalRow & ")"
        .Offset(2, 2) = "Min time under:"
        .Offset(2, 4).Formula = "=MIN(J2:J" & FinalRow & ")"
    End With
    End Sub

    Open in new window

    LVL 18

    Expert Comment


    The above code would update the info on Sheet 2 once you activate the Sheet2.


    Author Closing Comment

    Thanks! This is almost exactly what I was looking for!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now