Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance filtering with multiple conditons | Excel Discussion (Misc queries) | |||
Row filtering based on input box entry (column heading) | Excel Worksheet Functions | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions | |||
avanced filtering for latest date | Excel Discussion (Misc queries) | |||
Using Filtering | Excel Discussion (Misc queries) |