Filtering
 

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.

Filtering
 

Try...

A8, copied across:

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

Hope this helps!

In article ,
Mayank wrote:

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.

Filtering
 

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" wrote:

Try...

A8, copied across:

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

Hope this helps!

In article ,
Mayank wrote:

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.

Filtering
 

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

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

In article ,
Mayank wrote:

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.

Filtering
 

Thanks a lot it helps... ;)

"Domenic" wrote:

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

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

In article ,
Mayank wrote:

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.

Filtering
 

Hi Mayank....

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

Sub HowLowCanYouGo()
'Finds "bottom-most" (not smallest) value in comulms
'A:D 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" wrote:

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.

Filtering
 

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

"CLR" wrote:

Hi Mayank....

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

Sub HowLowCanYouGo()
'Finds "bottom-most" (not smallest) value in comulms
'A:D 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" wrote:

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.