Extract last and next-to-last entries in a range

T

Teri

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
 
B

Biff

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
 
A

Aladin Akyurek

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).
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
Click to expand...

--

[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.
 
S

Sandy Mann

Aladin,

With
=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("z",255),A2:A65536)))
Click to expand...

I get the last value again unless I add a *-1* after the MATCH function:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("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?

--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk


Aladin Akyurek said:
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).
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
Click to expand...

--

[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.
Click to expand...
 
B

Biff

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 said:
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).
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
Click to expand...

--

[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.
Click to expand...
 
A

Aladin Akyurek

Sandy said:
Aladin,

With



I get the last value again unless I add a *-1* after the MATCH function:

=LOOKUP(REPT("z",255),A2:INDEX(A2:A65536,MATCH(REPT("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?
Click to expand...

Sandy,

That's right. Thanks for catching that.
 
A

Aladin Akyurek

Biff said:
Hi!

Frequently, you post options to others replies and claim that they are more
efficient.
Click to expand...

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(REPT("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), are:

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
Click to expand...

In a not neglible number of cases one needs to profile formulas. Charles
Williams's FastExcel meets this need nicely.
 
B

Biff

Hi!
The issue in this thread wasn't one of efficiency, but correctness.
Click to expand...

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
 
K

Kwanjangnim

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
 
D

Domenic

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!
 
D

Domenic

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,MATCH(H1,A1:A6
,0),LPos-1))<>""),(INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B1: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,INDEX(B1:F6,MATCH(H1,A1:A
6,0),0))-1))

Hope this helps!
 
K

Kwanjangnim

Thanks alot, all formulas worked a treat

Domenic said:
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,MATCH(H1,A1:A6
,0),LPos-1))<>""),(INDEX(B1:F6,MATCH(H1,A1:A6,0),1):INDEX(B1: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,INDEX(B1:F6,MATCH(H1,A1:A
6,0),0))-1))

Hope this helps!
Click to expand...
 
B

Biff

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
 
Q

Quan

Thank you very much.

Biff said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suming Up Time over the course of days 1
Peak call times/hours 2
Time query 9
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Lookup and average data in an unsorted list 1
Referencing data across sheets 2
Count 1
Daily cumulative timesheet entries 0

Top