What do I use to find the last liste information in a row?

What function or macro would I use to return the last list information in a
given range of row even when some cells are left blank?

One way courtesy of Harlan Grove

=LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)

if you are looking horizontal just change to

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

--
Regards,

Peo Sjoblom


"BGarcia" <[email protected]> wrote in message
news:[email protected]...
> What function or macro would I use to return the last list information in
> a
> given range of row even when some cells are left blank?

Using Row1,

For *numeric* data *only*:
=LOOKUP(9.99999999999999E+307,1:1)

For *text* data *only*:
=LOOKUP(REPT("z",255),1:1)

For text or numbers:
=INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1:1)))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"BGarcia" <[email protected]> wrote in message
news:[email protected]...
> What function or macro would I use to return the last list information in
a
> given range of row even when some cells are left blank?

Leave it to Harlan to trump all the old standards.<g>

And since ISBLANK accepts total references,

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
should work OK, right?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> One way courtesy of Harlan Grove
>
> =LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
>
> if you are looking horizontal just change to
>
> =LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "BGarcia" <[email protected]> wrote in message
> news:[email protected]...
> > What function or macro would I use to return the last list information
in
> > a
> > given range of row even when some cells are left blank?
>

Raddyer,

This works perfect.....Thanks a bunch!



"Ragdyer" wrote:

> Using Row1,
>
> For *numeric* data *only*:
> =LOOKUP(9.99999999999999E+307,1:1)
>
> For *text* data *only*:
> =LOOKUP(REPT("z",255),1:1)
>
> For text or numbers:
> =INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1:1)))
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
> "BGarcia" <[email protected]> wrote in message
> news:[email protected]...
> > What function or macro would I use to return the last list information in
> a
> > given range of row even when some cells are left blank?
>
>

Appreciate the feed-back ... BUT ... don't you think Peo's suggestion is
more concise?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"BGarcia" <[email protected]> wrote in message
news:[email protected]...
Raddyer,

This works perfect.....Thanks a bunch!



"Ragdyer" wrote:

> Using Row1,
>
> For *numeric* data *only*:
> =LOOKUP(9.99999999999999E+307,1:1)
>
> For *text* data *only*:
> =LOOKUP(REPT("z",255),1:1)
>
> For text or numbers:
> =INDEX(1:1,MAX(MATCH({"zzzzzzz",9.9999999E+307},1:1)))
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
>
> "BGarcia" <[email protected]> wrote in message
> news:[email protected]...
> > What function or macro would I use to return the last list information
in
> a
> > given range of row even when some cells are left blank?
>
>

Hi RD

It appears that this formula will work on an entire row but not an entire
column.

I put a number in IV1 and used:

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)

Which worked.

I put a number in A65536 and used:

=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)

This did not work and returned 0.

If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.

Biff

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Leave it to Harlan to trump all the old standards.<g>
>
> And since ISBLANK accepts total references,
>
> =LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
> should work OK, right?
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
>> One way courtesy of Harlan Grove
>>
>> =LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
>>
>> if you are looking horizontal just change to
>>
>> =LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "BGarcia" <[email protected]> wrote in message
>> news:[email protected]...
>> > What function or macro would I use to return the last list information
> in
>> > a
>> > given range of row even when some cells are left blank?
>>
>

Sorry to be thick but...............
I tried the formula given by Peo :-

=LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)

It works perfectly but I don't understand how!

Isblank(a1:z1) returns false if there is data in the row which becomes 1 when the 1- is added. 1/(1-isblank(a1:z1) is therefore 1 if there is data or #div/0! if there is not.

When I use lookup it is lookup value, lookup range, column number - so what am I missing?

I noticed that also.

Maybe Harlan can explain?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Biff" <[email protected]> wrote in message
news:%[email protected]...
Hi RD

It appears that this formula will work on an entire row but not an entire
column.

I put a number in IV1 and used:

=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)

Which worked.

I put a number in A65536 and used:

=LOOKUP(2,1/(1-ISBLANK(A:A)),A:A)

This did not work and returned 0.

If I change the ref in ISBLANK to A1:A65535 or A2:A65536 it then worked.

Biff

"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Leave it to Harlan to trump all the old standards.<g>
>
> And since ISBLANK accepts total references,
>
> =LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
> should work OK, right?
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
>> One way courtesy of Harlan Grove
>>
>> =LOOKUP(2,1/(1-ISBLANK(A1:A1000)),A1:A1000)
>>
>> if you are looking horizontal just change to
>>
>> =LOOKUP(2,1/(1-ISBLANK(A1:Z1)),1:1)
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "BGarcia" <[email protected]> wrote in message
>> news:[email protected]...
>> > What function or macro would I use to return the last list information
> in
>> > a
>> > given range of row even when some cells are left blank?
>>
>

Ragdyer wrote...
....
>And since ISBLANK accepts total references,
>
>=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
>should work OK, right?
....

Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.

It has to be more complex then just a "number of cells (entries)" issue
Harlan, since A1:A65536 DOES work!
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
Ragdyer wrote...
....
>And since ISBLANK accepts total references,
>
>=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
>should work OK, right?
....

Only because whole row references are no big deal - only 256 entries.
Whole column references still fubar.

RagDyeR wrote...
>It has to be more complex then just a "number of cells (entries)"
issue
>Harlan, since A1:A65536 DOES work!
....

Not on my system. First, Excel *ALWAYS* replaces A1:A65536 with A:A.
Second, in a new worksheet, the following formula entered in B1

B1:
=SUMPRODUCT(--ISBLANK(A:A))

returns 0 rather than 65536. When I change the formula to

B1:
=SUMPRODUCT(--ISBLANK(A1:A65535))

it returns 65535. What results do you get on your system?

As for the formula in this thread, . . .

>Ragdyer wrote...
>...
>>And since ISBLANK accepts total references,
>>
>>=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
>>should work OK, right?
....

with A:A still all blank, I enter the following formula in B2.

B2:
=LOOKUP(2,1/(1-ISBLANK(A:A)),ROW(A:A))

It returns 1 to start with, not #N/A and not 0. If I change it to

B2:
=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))

it returns #N/A as expected (at least as I expect).

Reverting to the A:A formula, I then enter 9 in cell A11. The formula
*STILL* returns 1, not 11. What results do you get on your system?

You're skirting the issue Harlan!

I misspoke.
A1:A65536 *Doesn't* work.
A2:A65536 *OR* A1:A65535 *Both* work! ! !

You mentioned number of entries as the fubar of an A:A range.

All I said was it has to be more complex then that.
Come on ... 256 compared to 65,535!

Maybe Redmond has put all "entire column" references on the restricted list
because they're planning to spring that long awaited "Million" row XL on us
in the near future.<vbg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> RagDyeR wrote...
> >It has to be more complex then just a "number of cells (entries)"
> issue
> >Harlan, since A1:A65536 DOES work!
> ...
>
> Not on my system. First, Excel *ALWAYS* replaces A1:A65536 with A:A.
> Second, in a new worksheet, the following formula entered in B1
>
> B1:
> =SUMPRODUCT(--ISBLANK(A:A))
>
> returns 0 rather than 65536. When I change the formula to
>
> B1:
> =SUMPRODUCT(--ISBLANK(A1:A65535))
>
> it returns 65535. What results do you get on your system?
>
> As for the formula in this thread, . . .
>
> >Ragdyer wrote...
> >...
> >>And since ISBLANK accepts total references,
> >>
> >>=LOOKUP(2,1/(1-ISBLANK(1:1)),1:1)
> >>should work OK, right?
> ...
>
> with A:A still all blank, I enter the following formula in B2.
>
> B2:
> =LOOKUP(2,1/(1-ISBLANK(A:A)),ROW(A:A))
>
> It returns 1 to start with, not #N/A and not 0. If I change it to
>
> B2:
> =LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))
>
> it returns #N/A as expected (at least as I expect).
>
> Reverting to the A:A formula, I then enter 9 in cell A11. The formula
> *STILL* returns 1, not 11. What results do you get on your system?
>

Ragdyer wrote:
>You're skirting the issue Harlan!
>
>I misspoke.
....

I wasn't skirting the issue. You were being sloppy. I pointed that out.
You got defensive, and you're still in CYA mode.

>You mentioned number of entries as the fubar of an A:A range.
>
>All I said was it has to be more complex then that.
>Come on ... 256 compared to 65,535!

It's not complex, it's so simple it's almost laughable. 0-65535 is the
range of unsigned 16-bit (short) integers. (Speculation, but supported
by comments made by Gnumeric developers) Excel uses short integers
internally when iterating through arrays. Excel has no problem
iterating through millions of entries in individual arrays as long as
both dimensions are less then 65536. Just try

=SUMPRODUCT(ROW(1:65535)+65535*(COLUMN(A:BH)-1))

and compare the result to Gauss's formula for the sum of 65535*60
sequential integers starting from 1

=(65535*60)*(65535*60+1)/2

The dependence on short integers and 16-bit manuipulations using
bitwise operators is the main reason Excel will remain stuck with 65536
rows. Why it's still stuck with 256 columns is a better question since
there's nothing special and certainly no built-in support for 24-bit
(256 = 2^8 by 65536 = 2^16 = 2^24) integers.

It'd REQUIRE a new file format AND a fairly thorough rewrite of most of
Excel's source code to break through the 65536 row limit. Unless
Microsoft Office's market share drops under 60%, I don't see Microsoft
expending the resources to do it. The hopeful news is that as soon as
OpenOffice 2.0 hits production, some of the Calc developers may begin
to explore expanding Calc's worksheet cell count. If so, and if that
makes it into the OOo 3.0 design specs, we may be only a few years away
from Microsoft being forced (kicking & screaming) to devote serious
developer resources to a true major upgrade for Excel. It'd be the
first true major upgrade since Excel 2000 (OLAP, VBA6, major pivot
table improvements - vs 2002 with colored worksheet tabs and PITA
'Smart'Tags, or 2003 with fixes to problems with some stats functions
that were brought to Microsoft's attention over a decade ago).

Once again ... aaaas always ... the last word is yours.<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
Ragdyer wrote:
>You're skirting the issue Harlan!
>
>I misspoke.
....

I wasn't skirting the issue. You were being sloppy. I pointed that out.
You got defensive, and you're still in CYA mode.

>You mentioned number of entries as the fubar of an A:A range.
>
>All I said was it has to be more complex then that.
>Come on ... 256 compared to 65,535!

It's not complex, it's so simple it's almost laughable. 0-65535 is the
range of unsigned 16-bit (short) integers. (Speculation, but supported
by comments made by Gnumeric developers) Excel uses short integers
internally when iterating through arrays. Excel has no problem
iterating through millions of entries in individual arrays as long as
both dimensions are less then 65536. Just try

=SUMPRODUCT(ROW(1:65535)+65535*(COLUMN(A:BH)-1))

and compare the result to Gauss's formula for the sum of 65535*60
sequential integers starting from 1

=(65535*60)*(65535*60+1)/2

The dependence on short integers and 16-bit manuipulations using
bitwise operators is the main reason Excel will remain stuck with 65536
rows. Why it's still stuck with 256 columns is a better question since
there's nothing special and certainly no built-in support for 24-bit
(256 = 2^8 by 65536 = 2^16 = 2^24) integers.

It'd REQUIRE a new file format AND a fairly thorough rewrite of most of
Excel's source code to break through the 65536 row limit. Unless
Microsoft Office's market share drops under 60%, I don't see Microsoft
expending the resources to do it. The hopeful news is that as soon as
OpenOffice 2.0 hits production, some of the Calc developers may begin
to explore expanding Calc's worksheet cell count. If so, and if that
makes it into the OOo 3.0 design specs, we may be only a few years away
from Microsoft being forced (kicking & screaming) to devote serious
developer resources to a true major upgrade for Excel. It'd be the
first true major upgrade since Excel 2000 (OLAP, VBA6, major pivot
table improvements - vs 2002 with colored worksheet tabs and PITA
'Smart'Tags, or 2003 with fixes to problems with some stats functions
that were brought to Microsoft's attention over a decade ago).