Finding Last entry in a Column

  • Thread starter Thread starter MacroMike
  • Start date Start date
M

MacroMike

yesterday I put up a query because im trying to find a way of selectin
the last entry in a column then copying it intoa seperate cell. Thank
for the replys - someone got back to me saying to use


WorkSheets("Sheet1").Range("J65536").End(xlUp).Copy
WorkSheets("Sheet1").Range("J5")

Do I need to use a Macro for this?

Is there not an easier way where you can enter the formula into th
destination cell using =(??????)

Another problem I had with the above is that when i try it I get
message saynig data is out of range.

To recap what im trying to do - Im trying to find the last entry in
column between the ranges of N12:N65536 then copy this entry into on
of the header columns - in this case N8. All entries are numerica
integers.

All help much appreciated..




Mik
 
A little OT ..

In Excelforum's search page:
http://www.excelforum.com/search.php?s=c4f9199f36826cc94028aace3cb419a9

You could use, for example, the "Search By User Name" facility

and enter your user name: MacroMike

(with either "Match exact name" or "Match partial name")

Think this would return all* your posts made via Excelforum
and you can easily see Ron's response to your earlier post

*Recent ones. Older posts may have been purged from the forum's archives
 
yesterday I put up a query because im trying to find a way of selecting
the last entry in a column then copying it intoa seperate cell. Thanks
for the replys - someone got back to me saying to use


WorkSheets("Sheet1").Range("J65536").End(xlUp).Copy
WorkSheets("Sheet1").Range("J5")

Do I need to use a Macro for this?

Is there not an easier way where you can enter the formula into the
destination cell using =(??????)

Another problem I had with the above is that when i try it I get a
message saynig data is out of range.

To recap what im trying to do - Im trying to find the last entry in a
column between the ranges of N12:N65536 then copy this entry into one
of the header columns - in this case N8. All entries are numerical
integers.

All help much appreciated..
Click to expand...

If you keep things in the original thread, your followups will be more
coherent.

Also, you say you wanted a formula, but you do not give any information
relative to the formulaic solutions I posted in that thread. So it's difficult
to know what the problem was with those solutions.


--ron
 
Hi Mike,

You can use a combination of OFFSET and COUNT worksheet functions
like:

=OFFSET(N12,COUNT(N12:N65536),0)

You could also change this to COUNTIF if you need something a little
more sophisticated.

Good luck,

M@
 
Following on from Harlan's formula to find the last negative value in a
range try:

=LOOKUP(9.99999999999999E+307,N12:N65536)

if there is no number in the range then it will return #N/A. If you want to
hide that then use:

=IF(ISNA(LOOKUP(9.99999999999999E+307,N12:N65536)),"",LOOKUP(9.9999999999999
9E+307,N12:N65536))

The formula will ignore text. If you want the last text entry then use:

=IF(ISNA(LOOKUP(CHAR(255),N12:N65536)),"",LOOKUP(CHAR(255),N12:N65536))

unless you have a very strange text entry. The text formula will ignore
numbers (unless they are text numbers)

You can make the formula shorter by defining a name - say LastNo - Insert >
Name > Define and entering 9.99999999999999E+307 as the *Refers to* box
which would make the number formula:

=IF(ISNA(LOOKUP(LastNo,N12:N65536)),"",LOOKUP(LastNo,N12:N65536))

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


MacroMike > said:
yesterday I put up a query because im trying to find a way of selecting>
Click to expand...
the last entry in a column then copying it intoa seperate cell. Thanks
 
Sandy Mann wrote...
Following on from Harlan's formula to find the last negative
value in a range try:

=LOOKUP(9.99999999999999E+307,N12:N65536)
Click to expand...

IIRC, Aladin Arkyurek originated this LOOKUP idiom.
if there is no number in the range then it will return #N/A. If
you want to hide that then use:

=IF(ISNA(LOOKUP(9.99999999999999E+307,N12:N65536)),
"",LOOKUP(9.99999999999999E+307,N12:N65536))
Click to expand...
...

Better to use

=IF(COUNT(N12:N65536),LOOKUP(9.99999999999999E+307,N12:N65536),"")
The formula will ignore text. If you want the last text entry then
use:

=IF(ISNA(LOOKUP(CHAR(255),N12:N65536)),"",
LOOKUP(CHAR(255),N12:N65536))
Click to expand...
...

This actually doesn't conform to Excel's character collation sequence
REPT("z",255) is closer to the 'last' text value. Go on, test it
="z">CHAR(N) returns TRUE for all N other than 122, which is the ASCI
decimal code for 'z'.

To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536
 
hgrove > said:
Sandy Mann wrote...

IIRC, Aladin Arkyurek originated this LOOKUP idiom.

..

Better to use

=IF(COUNT(N12:N65536),LOOKUP(9.99999999999999E+307,N12:N65536),"")

..

This actually doesn't conform to Excel's character collation sequence.
REPT("z",255) is closer to the 'last' text value. Go on, test it:
="z">CHAR(N) returns TRUE for all N other than 122, which is the ASCII
decimal code for 'z'.

To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536)
Click to expand...

Nice

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Newsbeitrag [....]
To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536)
Click to expand...

Hi Harlan
agree with Peo: very nice!
But just one curious question. Do you know why this idiom does not work
if you use it with a column refererence like
=LOOKUP(2,1/(1-ISBLANK(N:N)),N:N)

Is this a problem of ISBLANK?


Regards
Frank
 
Frank Kabel said:
But just one curious question. Do you know why this idiom does not work
if you use it with a column refererence like
=LOOKUP(2,1/(1-ISBLANK(N:N)),N:N)
Click to expand...
....

It's not ISBLANK in particular, it's any array. No array can span 65536 rows
(or columns, and, yes indeed, arrays *can* span more than 256 columns as
long as you don't try to store them in a multiple cell range, e.g., enter
=ROW() in A1:A1000, then =SUM(TRANSPOSE(A1:A1000)) equals 500500, as
expected). The second argument to LOOKUP is an array expression, so limited
to 65535 rows. The hazards of using unsigned short integers.
 
Harlan Grove said:
...

It's not ISBLANK in particular, it's any array. No array can span 65536 rows
(or columns, and, yes indeed, arrays *can* span more than 256 columns as
long as you don't try to store them in a multiple cell range, e.g., enter
=ROW() in A1:A1000, then =SUM(TRANSPOSE(A1:A1000)) equals 500500, as
expected). The second argument to LOOKUP is an array expression, so limited
to 65535 rows. The hazards of using unsigned short integers.
Click to expand...

Hi Harlan
as always: thanks for the very specific/detailed explanation!


Regards
Frank
 
hgrove said:
This actually doesn't conform to Excel's character collatio
sequence. REPT("z",255) is closer to the 'last' text value. Go on
test it: ="z">CHAR(N) returns TRUE for all N other than 122, which i
the ASCII decimal code for 'z'.

To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536) [/B]
Click to expand...

Thanks for all your help guys - this worked best - :
 
Thank you Harlan,

To rephrase what Frank said, not just an answer but a masterclass in Excel.
This actually doesn't conform to Excel's character collation sequence.
REPT("z",255) is closer to the 'last' text value. Go on, test it:
="z">CHAR(N) returns TRUE for all N other than 122, which is the ASCII
decimal code for 'z'.
Click to expand...

Yes I did test it and I get four false results - but all versions of 'z' as
you said. I must confess this leaves me baffled unless all the 'higher'
characters all somehow equate to lower order characters like the different
versions of 'z' equate to 'z'
To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536)
Click to expand...

May I add my own inadequate comment to Peo & Frank's nice.

Finally my apologies to Aladin Akyurek for not knows that he was the
originator of this *this LOOKUP idiom*

Regards,

Sandy
 
Sandy Mann said:
Thank you Harlan,

To rephrase what Frank said, not just an answer but a masterclass in Excel.
Click to expand...

It was Harlan who pointed this out to you - not me :-)


Frank
 
hgrove said:
To get the last entry in a column regardless of type use

=LOOKUP(2,1/(1-ISBLANK(N12:N65536)),N12:N65536)
Click to expand...

Hi Harlan, interesting formula, but I'm curious about speed. I often
have very large spreadsheets using lookup functions and calculation
time can be significant on a slow computer. I haven't tested it, but
would the OFFSET formula I gave earlier in this thread be quicker than
a LOOKUP based formula? Obviously by comparison there were
limitations to the OFFSET formula I suggested, but some modifications
could be made.

Thanks
M@
 
Matt Dalkie said:
hgrove <<[email protected]>> wrote in message

Hi Harlan, interesting formula, but I'm curious about speed. I often
have very large spreadsheets using lookup functions and calculation
time can be significant on a slow computer. I haven't tested it, but
would the OFFSET formula I gave earlier in this thread be quicker than
a LOOKUP based formula? Obviously by comparison there were
limitations to the OFFSET formula I suggested, but some modifications
could be made.
Click to expand...


Hi Matt
there's a difference in both formulas. Your formula won't work if
there're blank rows in between. The cOUNT function would give you a
wrong result.

Frank
 
Frank Kabel said:
It was Harlan who pointed this out to you - not me :-)


Frank
Click to expand...


Yes I know Frank. What I meant was:

Start of 1st comment-----------------------------------------

To rephrase what Frank said, not just an answer but a masterclass in Excel.

End of 1st comment--------------------------------------

Start of 2nd comment------------------------------------
This actually doesn't conform to Excel's character collation sequence.
REPT("z",255) is closer to the 'last' text value. Go on, test it:
="z">CHAR(N) returns TRUE for all N other than 122, which is the ASCII
decimal code for 'z'.
Click to expand...

Yes I did test it and I get four false results - but all versions of 'z' as
you said. I must confess this leaves me baffled unless all the 'higher'
characters all somehow equate to lower order characters like the different
versions of 'z' equate to 'z'

End of 2nd comment--------------------------------------

What chance do I have with Excel if I can't even post clearly? <g>

Sandy
 
Back
Top