Remember Me?

#11
October 18th 05, 10:25 AM
 Max Posts: n/a
Find Last Row in Column

You're welcome !

A GREAT PAPER INDEED!

Sentiments shared, though not w/o a tinge of sadness*
*in memory of Frank Kabel
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
wrote in message
oups.com...
Hi Max,

*inspired by the excellent white paper by Frank Kabel & Bob Phillips at:
http://www.xldynamic.com/source/xld.....html#last_any

A GREAT PAPER INDEED!

Brgds Sige

#12
October 18th 05, 10:57 AM
Find Last Row in Column

Taking column A as target...

1. Focusing only on data that is either numeric or text...

B1:

=MATCH(REPT("z",255),A:A)

B2:

=MAX(CHOOSE({1,2},IF(ISNUMBER(B1),B1,0),MATCH(9.99 999999999999E+307,A:A)))

2. Focus is on any value...

=IF(ISBLANK(A65536),MATCH(2,1/(1-ISBLANK(A1:A65535))),65536)

which must be confirmed with control+shift+enter.

This yields the native row number of even an error value if that is the
last value.

Taking in consideration your data types, he first is more efficient.

wrote:

A column with text- and numerci values. No formulas though.

Brgds Sige

#13
October 18th 05, 11:03 AM
Find Last Row in Column

Two notes...

This will fail when there is either no text or no numeric data. BTW, the
OP is asking for the row number, not the address of the last
numeric/text value.

You state:
"Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs."

Maybe so. However, did you consider endless variations the people may
come up with, causing a jungle of constants, hard to explain to new comers?

Roger Govier wrote:
Hi

One way

This will look for the last numeric or last text value in column A and
return the cell reference where found.
Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs.

Regards

Roger Govier

wrote:

Hi TemplateBuilder,

Thank you for your solution but that is not exactly what I am after ...

Imagine you have only 5 values filled in Column A ...bu the 5th is at
row 125 then I would like it to return 125 and not 5 (or 6).

Brgds Sige

--

[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.
#14
October 18th 05, 11:59 AM
 Roger Govier Posts: n/a
Find Last Row in Column

Thank you for the notes.
This will fail when there is either no text or no numeric data.

I had rather assumed, that if the OP had a totally empty column, then he
wouldn't be looking for the last used row, hence the #N/A return from a
totally blank column didn't really seem to be material.

BTW, the OP is asking for the row number, not the address

Yes, on re-reading the original post, the OP does ask for row and not cell
address. The formula should be amended to

=CELL("Row",INDEX(A:A,MAX(MATCH(99999999,A:A),MATC H("ZZZZ",A:A))))

However, did you consider endless variations the people may come up with,
causing a jungle of constants, hard to explain to new comers?

No, I didn't. But I did give the formula for a large numeric value or large
text value if the user chose to use those instead.

Neither did I go on to explain that in reality, I use defined names for
large numbers and large text values, which I use in my formulae rather than
having to count how many 9's I am entering every time I use this type of
Lookup, and enabling the call of the REPT() function to be made once, rather
than throughout every formula.

InsertNameDefine
Name BigN Refers to =9.99999999999999E+307
Name BigT Refers to =REPT("Z",255)
(these were tips I picked up from Bob Phillips)

=CELL("Row",INDEX(A:A,MAX(MATCH(BigN,A:A),MATCH(Bi gT,A:A))))

I think it is sometimes of more value to the OP to get a rapid answer that
answers their immediate need, rather than wait longer for a comprehensive

But then again, perhaps I am wrong.<g

Regards

Roger Govier

Two notes...

This will fail when there is either no text or no numeric data. BTW, the
OP is asking for the row number, not the address of the last
numeric/text value.

You state:
"Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs."

Maybe so. However, did you consider endless variations the people may
come up with, causing a jungle of constants, hard to explain to new comers?

Roger Govier wrote:

Hi

One way

This will look for the last numeric or last text value in column A and
return the cell reference where found.
Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255)
for text, but I have usually found the above to meet my needs.

Regards

Roger Govier

wrote:

Hi TemplateBuilder,

Thank you for your solution but that is not exactly what I am after ...

Imagine you have only 5 values filled in Column A ...bu the 5th is at
row 125 then I would like it to return 125 and not 5 (or 6).

Brgds Sige

#15
October 18th 05, 12:26 PM
Find Last Row in Column

Roger Govier wrote:

Thank you for the notes.
This will fail when there is either no text or no numeric data.

I had rather assumed, that if the OP had a totally empty column, then he
wouldn't be looking for the last used row, hence the #N/A return from a
totally blank column didn't really seem to be material.

That is not what I mean: When it's the case that A is all numeric or
when when A is all text, the formula will fail.

BTW, the OP is asking for the row number, not the address

Yes, on re-reading the original post, the OP does ask for row and not
cell address. The formula should be amended to

=CELL("Row",INDEX(A:A,MAX(MATCH(99999999,A:A),MATC H("ZZZZ",A:A))))

Too wordy... <g and still subject to the criticism I forwarded.

However, did you consider endless variations the people may come up

with, causing a jungle of constants, hard to explain to new comers?
No, I didn't. But I did give the formula for a large numeric value or
large text value if the user chose to use those instead.

Neither did I go on to explain that in reality, I use defined names for
large numbers and large text values, which I use in my formulae rather
than having to count how many 9's I am entering every time I use this
type of Lookup, and enabling the call of the REPT() function to be made
once, rather than throughout every formula.

InsertNameDefine
Name BigN Refers to =9.99999999999999E+307
Name BigT Refers to =REPT("Z",255)
(these were tips I picked up from Bob Phillips)

I for one often called them: BigNum and BigStr.

=CELL("Row",INDEX(A:A,MAX(MATCH(BigN,A:A),MATCH(Bi gT,A:A))))

I think it is sometimes of more value to the OP to get a rapid answer
that answers their immediate need, rather than wait longer for a

But then again, perhaps I am wrong.<g

You are... <vbg. Seriously, I admit I tend to be kosher about things
like that.

Regards

Roger Govier

Two notes...

This will fail when there is either no text or no numeric data. BTW,
the OP is asking for the row number, not the address of the last
numeric/text value.

You state:
"Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255) for
text, but I have usually found the above to meet my needs."

Maybe so. However, did you consider endless variations the people may
come up with, causing a jungle of constants, hard to explain to new
comers?

Roger Govier wrote:

Hi

One way

This will look for the last numeric or last text value in column A
and return the cell reference where found.
Some people use the largest number Excel can handle
(9.99999999999999E+307) in the search for numbers, and REPT("Z",255)
for text, but I have usually found the above to meet my needs.

Regards

Roger Govier

wrote:

Hi TemplateBuilder,

Thank you for your solution but that is not exactly what I am after ...

Imagine you have only 5 values filled in Column A ...bu the 5th is at
row 125 then I would like it to return 125 and not 5 (or 6).

Brgds Sige

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Anna / Ideal Excel Worksheet Functions 14 August 10th 05 04:28 PM Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM vrk1 Excel Discussion (Misc queries) 2 April 30th 05 12:17 AM Luanne Excel Discussion (Misc queries) 4 April 29th 05 08:32 PM vikasarora07 Excel Discussion (Misc queries) 1 April 12th 05 03:47 PM

All times are GMT +1. The time now is 12:59 PM.