Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-12-28
Medium Priority
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
  • 3
  • 2
LVL 18

Expert Comment

ID: 26138157

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

ID: 26138622
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

krishnakrkc earned 2000 total points
ID: 26138986

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

ID: 26138996

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


Author Closing Comment

ID: 31670396
Thanks! This is almost exactly what I was looking for!

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 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