• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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

Hello,

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)

thanks,

paul
Example-.xlsx
0
pweber_ep
Asked:
pweber_ep
  • 3
  • 2
1 Solution
 
krishnakrkcCommented:
Hi,

Use advanced filter.

K1 must be empty.In K2 on Accel,

=OR(I2={"begin","end"})


Define names

SourceRng

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

Criteria

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

Now select sheet2

goto Data > Advanced filter

See image for rest.

Kris
AdvFilter.jpg
0
 
pweber_epAuthor Commented:
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?
0
 
krishnakrkcCommented:
Hi,

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

Kris
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

0
 
krishnakrkcCommented:

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

Kris
0
 
pweber_epAuthor Commented:
Thanks! This is almost exactly what I was looking for!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now