Filtering

[Mayank]

I am looking for a query(functions) for example to be implemented on a table
where a column has a few numeric values with perhaps even some rows vacant.
In the final result row I want the last numeric value of the column to be
printed.

For example to illustrate:

A B C D
1 1.2 2.1
2 3.5 9.3 2.3
3 4.9 5.5 4.6
4 2.3
5 4.1
..
..
8 2.3 4.9 5.5 4.1

Here the result row is number(8) where the results from the above columns
are printed. The results only follow one rule that is the last numeric value
is printed irrespective of whether if its smallest or largest value in that
column.

 

[Domenic]

Try...

A8, copied across:

=LOOKUP(9.99999999999999E+307,A1:A7)

Hope this helps!

 

[Mayank]

Thanks a lot Domenic it works but can you explain how does
9.999999999999999E+307 is actually looking for the last numeric value in the
column.

 

[Domenic]

Have a look at Aladin's explanation in the following thread...

http://www.mrexcel.com/board2/viewtopic.php?t=105725

 

[Mayank]

Thanks a lot it helps...

Have a look at Aladin's explanation in the following thread...

http://www.mrexcel.com/board2/viewtopic.php?t=105725

 

[CLR]

Hi Mayank....

Here's a VBA approach.........(tailored to your sample data),

Sub HowLowCanYouGo()
'Finds "bottom-most" (not smallest) value in comulms
'A and places that value in Row 8

Range("a8:d8").ClearContents

Application.Goto Reference:="R65000C1"
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Range("A8").Select
ActiveSheet.Paste

Application.Goto Reference:="R65000C2"
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
ActiveSheet.Paste

Application.Goto Reference:="R65000C3"
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Range("c8").Select
ActiveSheet.Paste

Application.Goto Reference:="R65000C4"
Selection.End(xlUp).Select
Application.CutCopyMode = False
Selection.Copy
Range("d8").Select
ActiveSheet.Paste

End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Mayank]

Hi CLR, but I was looking for a fn than a VBA. Still thanks a lot for the help