| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Extract last and next-to-last entries in a range
I must set up a spreadsheet and extract the last and next-to-last entries in
certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#2
|
|||
|
|||
|
Hi!
Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#3
|
|||
|
|||
|
Ooops!
Typo: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) Should be: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) Biff "Biff" wrote in message ... Hi! Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#4
|
|||
|
|||
|
You are a LIFESAVER! That worked perfectly! Teri -- tbitler ------------------------------------------------------------------------ tbitler's Profile: http://www.excelforum.com/member.php...o&userid=25538 View this thread: http://www.excelforum.com/showthread...hreadid=389738 |
|
#5
|
|||
|
|||
|
Assuming that the data start at row 2 in column A and the values of
interest are text... Last text value: =LOOKUP(REPT("z",255),A2:A65536) Next to last value: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536))) This formulas would return a formula-blank (i.e., "") if such is the last (or the next-to-last value). Teri wrote: I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#7
|
|||
|
|||
|
Hi!
Frequently, you post options to others replies and claim that they are more efficient. I'm just wondering how you test these for efficiency? Particularly, the speed of calculation. Do you use some type of benchmarking software or do you have VBA routines that do this? I'm always interested in improving my approaches to problems so any insight you can offer would be greatly appreciated. Biff "Aladin Akyurek" wrote in message ... Assuming that the data start at row 2 in column A and the values of interest are text... Last text value: =LOOKUP(REPT("z",255),A2:A65536) Next to last value: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536))) This formulas would return a formula-blank (i.e., "") if such is the last (or the next-to-last value). Teri wrote: I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#8
|
|||
|
|||
|
Sandy Mann wrote:
Aladin, With =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(R EPT("z",255),A2:A65536))) I get the last value again unless I add a *-1* after the MATCH function: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536)-1)) My last test data was in row 27 and the MATCH function returned 26. When the INDEX indexed 26 down from A2 it of course found the last entry in A27. Actually now that I have selected *Show downloaded messages* I see that Biff's reply included the -1. Is it required in you formula or am I missing something? Sandy, That's right. Thanks for catching that. |
|
#9
|
|||
|
|||
|
Biff wrote:
Hi! Frequently, you post options to others replies and claim that they are more efficient. Biff, The issue in this thread wasn't one of efficiency, but correctness. Given: New York New Jersey Empty Empty Ohio in A2:A6 =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) cannot capture the next-to-last text value. While, with missing -1 added... =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536)-1)) will do so. Regarding efficiency issues, a recent trend is http://tinyurl.com/axrvo where I "claimed" a certain formula to be more efficent than another. That is: =LOOKUP(9.99999999999999E+307,L:L) is efficient compared to =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) for the task of fetching the last numeric value from a range, consisting of either manual entries or calculated values. There is no need to back up this particular claim with any benchmarking or timing software. See the discussion in that thread for why this should be so. You also don't need to construct a temporal profile to claim that: =SUMIF($C$4:$C$15,"S",$H$4:$H$*15) is faster than: =SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15) or {=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)} for SumIf operates on range objects, not on array objects as the latter two must, therefore faster. To add just another example... =LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN ","D";"YY",*"P"}) has a better temporal score than =IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P ",IF(OR(AND*(E2="Y",F2="N"), AND(E2="N",F2="Y")),"D",""))) Do we need to profile them? I don't think so. Some rules of thumb, derived from the knowledge of the behavior of the functions (possible underlying algorithms the functions invoke), a 1. Calculating on range objects is faster than calculating on array objects. 2. Lookup functions that resort to binary search are faster than lookup functions which are set up to invoke linear search. 3. A formula with lesser number of function calls, all things being equal, is better than one that invokes a multitude of functions. 4. The formulas without volatile functions are generally faster than the formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better than INDIRECT(), couppled with ADDRESS(). 5. Calculating on the relevant subranges is faster than on the whole range. The foregoing list is by no means exhaustive. I'm just wondering how you test these for efficiency? Particularly, the speed of calculation. Do you use some type of benchmarking software or do you have VBA routines that do this? I'm always interested in improving my approaches to problems so any insight you can offer would be greatly appreciated. Biff In a not neglible number of cases one needs to profile formulas. Charles Williams's FastExcel meets this need nicely. "Aladin Akyurek" wrote in message ... Assuming that the data start at row 2 in column A and the values of interest are text... Last text value: =LOOKUP(REPT("z",255),A2:A65536) Next to last value: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(R EPT("z",255),A2:A65536))) This formulas would return a formula-blank (i.e., "") if such is the last (or the next-to-last value). |
|
#10
|
|||
|
|||
|
Hi!
The issue in this thread wasn't one of efficiency, but correctness. Well, let's see about that! <g Your formula uses logic that accounts for empty cells in the range, however, the OP's sample data did not include any empty cells in the range. There was also no mention of the possibility of empty cells in the range. So I based my formulas on what was posted. While it is probably not a good practice to assume things that are not posted, it's good to be prepared for the possibilities. But taking that into account and by the same token, then you also have done what you want to correct me for. Your formulas assume the values in the range are text. They may have been formatted date/times. There may be both text and numeric values in the range. If you're going to account for the possibility of empty cells in the range then shouldn't you also account for mixed data types? Gotcha! <g Thanks for the info regarding efficiency. I appreciate the contributions you make here and have learned a great deal from you. Biff "Aladin Akyurek" wrote in message ... Biff wrote: Hi! Frequently, you post options to others replies and claim that they are more efficient. Biff, The issue in this thread wasn't one of efficiency, but correctness. Given: New York New Jersey Empty Empty Ohio in A2:A6 =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) cannot capture the next-to-last text value. While, with missing -1 added... =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536)-1)) will do so. Regarding efficiency issues, a recent trend is http://tinyurl.com/axrvo where I "claimed" a certain formula to be more efficent than another. That is: =LOOKUP(9.99999999999999E+307,L:L) is efficient compared to =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) for the task of fetching the last numeric value from a range, consisting of either manual entries or calculated values. There is no need to back up this particular claim with any benchmarking or timing software. See the discussion in that thread for why this should be so. You also don't need to construct a temporal profile to claim that: =SUMIF($C$4:$C$15,"S",$H$4:$H$*15) is faster than: =SUMPRODUCT(--($C$4:$C$15="S")*,$H$4:$H$15) or {=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)} for SumIf operates on range objects, not on array objects as the latter two must, therefore faster. To add just another example... =LOOKUP(E2&F2,{"N","";"NN","";*"NY","D";"Y","";"YN ","D";"YY",*"P"}) has a better temporal score than =IF(OR(E2="",F2=""),"",IF(COUN*TIF(E2:F2,"Y")=2,"P ",IF(OR(AND*(E2="Y",F2="N"), AND(E2="N",F2="Y")),"D",""))) Do we need to profile them? I don't think so. Some rules of thumb, derived from the knowledge of the behavior of the functions (possible underlying algorithms the functions invoke), a 1. Calculating on range objects is faster than calculating on array objects. 2. Lookup functions that resort to binary search are faster than lookup functions which are set up to invoke linear search. 3. A formula with lesser number of function calls, all things being equal, is better than one that invokes a multitude of functions. 4. The formulas without volatile functions are generally faster than the formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better than INDIRECT(), couppled with ADDRESS(). 5. Calculating on the relevant subranges is faster than on the whole range. The foregoing list is by no means exhaustive. I'm just wondering how you test these for efficiency? Particularly, the speed of calculation. Do you use some type of benchmarking software or do you have VBA routines that do this? I'm always interested in improving my approaches to problems so any insight you can offer would be greatly appreciated. Biff In a not neglible number of cases one needs to profile formulas. Charles Williams's FastExcel meets this need nicely. "Aladin Akyurek" wrote in message ... Assuming that the data start at row 2 in column A and the values of interest are text... Last text value: =LOOKUP(REPT("z",255),A2:A65536) Next to last value: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH( REPT("z",255),A2:A65536))) This formulas would return a formula-blank (i.e., "") if such is the last (or the next-to-last value). |
|
#11
|
|||
|
|||
|
Extract last and next-to-last entries in a range
How can i use this function with a vlookup command
i would like to look up a name and return the last and next to last as per original question but with a vlookup command added, i've created the my range and each name has its own row for data entry, can you help, thanks "Aladin Akyurek" wrote: Biff wrote: Hi! Frequently, you post options to others replies and claim that they are more efficient. Biff, The issue in this thread wasn't one of efficiency, but correctness. Given: New York New Jersey Empty Empty Ohio in A2:A6 =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) cannot capture the next-to-last text value. While, with missing -1 added... =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REP T("z",255),A2:A65536)-1)) will do so. Regarding efficiency issues, a recent trend is http://tinyurl.com/axrvo where I "claimed" a certain formula to be more efficent than another. That is: =LOOKUP(9.99999999999999E+307,L:L) is efficient compared to =LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000) for the task of fetching the last numeric value from a range, consisting of either manual entries or calculated values. There is no need to back up this particular claim with any benchmarking or timing software. See the discussion in that thread for why this should be so. You also don't need to construct a temporal profile to claim that: =SUMIF($C$4:$C$15,"S",$H$4:$H$Â*15) is faster than: =SUMPRODUCT(--($C$4:$C$15="S")Â*,$H$4:$H$15) or {=SUM(IF($C$4:$C$15="S",,$H$4:$H$15)} for SumIf operates on range objects, not on array objects as the latter two must, therefore faster. To add just another example... =LOOKUP(E2&F2,{"N","";"NN","";Â*"NY","D";"Y","";"Y N","D";"YY",Â*"P"}) has a better temporal score than =IF(OR(E2="",F2=""),"",IF(COUNÂ*TIF(E2:F2,"Y")=2," P",IF(OR(ANDÂ*(E2="Y",F2="N"), AND(E2="N",F2="Y")),"D",""))) Do we need to profile them? I don't think so. Some rules of thumb, derived from the knowledge of the behavior of the functions (possible underlying algorithms the functions invoke), a 1. Calculating on range objects is faster than calculating on array objects. 2. Lookup functions that resort to binary search are faster than lookup functions which are set up to invoke linear search. 3. A formula with lesser number of function calls, all things being equal, is better than one that invokes a multitude of functions. 4. The formulas without volatile functions are generally faster than the formulas which invoke INDIRECT, OFFSET, etc. Also, OFFSET() is better than INDIRECT(), couppled with ADDRESS(). 5. Calculating on the relevant subranges is faster than on the whole range. The foregoing list is by no means exhaustive. I'm just wondering how you test these for efficiency? Particularly, the speed of calculation. Do you use some type of benchmarking software or do you have VBA routines that do this? I'm always interested in improving my approaches to problems so any insight you can offer would be greatly appreciated. Biff In a not neglible number of cases one needs to profile formulas. Charles Williams's FastExcel meets this need nicely. "Aladin Akyurek" wrote in message ... Assuming that the data start at row 2 in column A and the values of interest are text... Last text value: =LOOKUP(REPT("z",255),A2:A65536) Next to last value: =LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(R EPT("z",255),A2:A65536))) This formulas would return a formula-blank (i.e., "") if such is the last (or the next-to-last value). |
|
#12
|
|||
|
|||
|
Extract last and next-to-last entries in a range
I'm not sure to which original question you're referring, but see if the
following helps... Assumptions: A1:B10 contains your lookup table C1 contains your lookup value Formulas: Last... =LOOKUP(2,1/(A1:A10=C1),B1:B10) Next to last... =INDEX(B1:B10,LARGE(IF(A1:A10=C1,ROW(A1:A10)-ROW(A1)+1),2)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Kwanjangnim" wrote: i would like to look up a name and return the last and next to last as per original question but with a vlookup command added, i've created the my range and each name has its own row for data entry, can you help, thanks |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
hi domenic,
i didn't understand your answer, but i've setup an example of what i'm trying to do A B C D E f g h i 1 name 1 z zz zzz zzzz zzzzz 2 name 2 y yy yyy yyyy yyyyy 3 name 3 g gg ggg gggg ggggg 4 name 4 h hh hhh hhhh hhhhh 5 name 5 i ii iii iiii iiiii 6 name 6 f ff fff ffff fffff i want to lookup a name and return the value of the last entered data within that row, this lookup is done on a another wooksheet within the wookbook after that in an adjacent cell i would like to return the next to last value - yes there will be blank cells within each row. e.g look up 'name 2' and return last value = 'yyyyy' and next to last 'yyyy' hope thats clear and thanks for your help so far ========================================= "Domenic" wrote: I'm not sure to which original question you're referring, but see if the following helps... Assumptions: A1:B10 contains your lookup table C1 contains your lookup value Formulas: Last... =LOOKUP(2,1/(A1:A10=C1),B1:B10) Next to last... =INDEX(B1:B10,LARGE(IF(A1:A10=C1,ROW(A1:A10)-ROW(A1)+1),2)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Kwanjangnim" wrote: i would like to look up a name and return the last and next to last as per original question but with a vlookup command added, i've created the my range and each name has its own row for data entry, can you help, thanks |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
Assumptions:
A1:F6 contains your data H1 contains your lookup value, such as 'Name 2' Formulas: If your data contains text values, and blanks are actually blank cells not 'formula' blanks, try... Last: =LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),0)) Second to last: =LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),1):INDEX(B1:F6,MATCH( H1,A1:A6,0),MATCH(REPT("z",255),INDEX(B1:F6,MATCH( H1,A1:A6,0),0))-1)) If your data contains text values, and blanks are actually 'formula' blanks, try... Last: =LOOKUP(2,1/(INDEX(B1:F6,MATCH(H1,A1:A6,0),0)<""),INDEX(B1:F6 ,MATCH(H1,A 1:A6,0),0)) Second to last: First, define the following reference... Insert Name Define Name: LPos Refers to: =MATCH(2,1/(INDEX(Sheet1!$B$1:$F$6,MATCH(Sheet1!$H$1,Sheet1!$ A$1:$A$6,0), 0)<"")) Click Ok Then, try the following formula... =LOOKUP(2,1/((INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B1:F6,MAT CH(H1,A1:A6 ,0),LPos-1))<""),(INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B 1:F6,MATCH(H1, A1:A6,0),LPos-1))) If your data contains numerical values, try... Last: =LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),0)) Second to last: =LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),1):INDEX(B1:F 6,MATCH(H1,A1:A6,0),MATCH(9.99999999999999E+307,IN DEX(B1:F6,MATCH(H1,A1:A 6,0),0))-1)) Hope this helps! In article , "Kwanjangnim" wrote: hi domenic, i didn't understand your answer, but i've setup an example of what i'm trying to do A B C D E f g h i 1 name 1 z zz zzz zzzz zzzzz 2 name 2 y yy yyy yyyy yyyyy 3 name 3 g gg ggg gggg ggggg 4 name 4 h hh hhh hhhh hhhhh 5 name 5 i ii iii iiii iiiii 6 name 6 f ff fff ffff fffff i want to lookup a name and return the value of the last entered data within that row, this lookup is done on a another wooksheet within the wookbook after that in an adjacent cell i would like to return the next to last value - yes there will be blank cells within each row. e.g look up 'name 2' and return last value = 'yyyyy' and next to last 'yyyy' hope thats clear and thanks for your help so far |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
Thanks alot, all formulas worked a treat
"Domenic" wrote: Assumptions: A1:F6 contains your data H1 contains your lookup value, such as 'Name 2' Formulas: If your data contains text values, and blanks are actually blank cells not 'formula' blanks, try... Last: =LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),0)) Second to last: =LOOKUP(REPT("z",255),INDEX(B1:F6,MATCH(H1,A1:A6,0 ),1):INDEX(B1:F6,MATCH( H1,A1:A6,0),MATCH(REPT("z",255),INDEX(B1:F6,MATCH( H1,A1:A6,0),0))-1)) If your data contains text values, and blanks are actually 'formula' blanks, try... Last: =LOOKUP(2,1/(INDEX(B1:F6,MATCH(H1,A1:A6,0),0)<""),INDEX(B1:F6 ,MATCH(H1,A 1:A6,0),0)) Second to last: First, define the following reference... Insert Name Define Name: LPos Refers to: =MATCH(2,1/(INDEX(Sheet1!$B$1:$F$6,MATCH(Sheet1!$H$1,Sheet1!$ A$1:$A$6,0), 0)<"")) Click Ok Then, try the following formula... =LOOKUP(2,1/((INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B1:F6,MAT CH(H1,A1:A6 ,0),LPos-1))<""),(INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B 1:F6,MATCH(H1, A1:A6,0),LPos-1))) If your data contains numerical values, try... Last: =LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),0)) Second to last: =LOOKUP(9.99999999999999E+307,INDEX(B1:F6,MATCH(H1 ,A1:A6,0),1):INDEX(B1:F 6,MATCH(H1,A1:A6,0),MATCH(9.99999999999999E+307,IN DEX(B1:F6,MATCH(H1,A1:A 6,0),0))-1)) Hope this helps! In article , "Kwanjangnim" wrote: hi domenic, i didn't understand your answer, but i've setup an example of what i'm trying to do A B C D E f g h i 1 name 1 z zz zzz zzzz zzzzz 2 name 2 y yy yyy yyyy yyyyy 3 name 3 g gg ggg gggg ggggg 4 name 4 h hh hhh hhhh hhhhh 5 name 5 i ii iii iiii iiiii 6 name 6 f ff fff ffff fffff i want to lookup a name and return the value of the last entered data within that row, this lookup is done on a another wooksheet within the wookbook after that in an adjacent cell i would like to return the next to last value - yes there will be blank cells within each row. e.g look up 'name 2' and return last value = 'yyyyy' and next to last 'yyyy' hope thats clear and thanks for your help so far |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
What if your data are a mixture of text and numbers or equations?
"Biff" wrote: Ooops! Typo: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) Should be: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) Biff "Biff" wrote in message ... Hi! Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
Hi!
This will return the last and next to last cells that contain any value*: Entered as an array using the key combination of CTRL,SHIFT,ENTER: For the last value: =INDEX(A$1:A$20,LARGE((A$1:A$20<"")*(ROW(A$1:A$20 )),ROWS($1:1))) Just copy down to get the next to the last value. * - if you have a formula that returns a formula blank ("") this formula will not pick that up. See this for an extensive overview: http://xldynamic.com/source/xld.LastValue.html Biff "Quan" wrote in message ... What if your data are a mixture of text and numbers or equations? "Biff" wrote: Ooops! Typo: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) Should be: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) Biff "Biff" wrote in message ... Hi! Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
Thank you very much.
"Biff" wrote: Hi! This will return the last and next to last cells that contain any value*: Entered as an array using the key combination of CTRL,SHIFT,ENTER: For the last value: =INDEX(A$1:A$20,LARGE((A$1:A$20<"")*(ROW(A$1:A$20 )),ROWS($1:1))) Just copy down to get the next to the last value. * - if you have a formula that returns a formula blank ("") this formula will not pick that up. See this for an extensive overview: http://xldynamic.com/source/xld.LastValue.html Biff "Quan" wrote in message ... What if your data are a mixture of text and numbers or equations? "Biff" wrote: Ooops! Typo: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) Should be: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) Biff "Biff" wrote in message ... Hi! Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract last and next-to-last entries in a range
You're welcome!
Biff "Quan" wrote in message ... Thank you very much. "Biff" wrote: Hi! This will return the last and next to last cells that contain any value*: Entered as an array using the key combination of CTRL,SHIFT,ENTER: For the last value: =INDEX(A$1:A$20,LARGE((A$1:A$20<"")*(ROW(A$1:A$20 )),ROWS($1:1))) Just copy down to get the next to the last value. * - if you have a formula that returns a formula blank ("") this formula will not pick that up. See this for an extensive overview: http://xldynamic.com/source/xld.LastValue.html Biff "Quan" wrote in message ... What if your data are a mixture of text and numbers or equations? "Biff" wrote: Ooops! Typo: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) Should be: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-1) Biff "Biff" wrote in message ... Hi! Assuming that the entries are text values: For the next to the last entry: =INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2) For the last entry: =LOOKUP(REPT("Z",255),A:A) Biff "Teri" wrote in message ... I must set up a spreadsheet and extract the last and next-to-last entries in certain columns. Those values will be linked to another sheet in the workbook entitled "Summary". The columns are set up as shown below. I have a second set of columns which are from 14:00 Friday July 29 through 8:00 Saturday July 30. I know this is a lot of info, but I'm such a novice at this. Any help would be greatly appreciated! City Mile Marker Odometer 19:00 Thursday July 28 20:00 Thursday July 28 21:00 Thursday July 28 22:00 Thursday July 28 23:00 Thursday July 28 24:00 Thursday July 28 01:00 Friday July 29 02:00 Friday July 29 03:00 Friday July 29 04:00 Friday July 29 05:00 Friday July 29 06:00 Friday July 29 07:00 Friday July 29 08:00 Friday July 29 09:00 Friday July 29 10:00 Friday July 29 11:00 Friday July 29 12:00 Friday July 29 13:00 Friday July 29 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
