Search Search for: Business ApplicationsAll Forums

# EXCEL question -- last entry in column

 KJS2
Junior Member with 4 posts.

Join Date: Jan 2005
Experience: Intermediate
27-Jan-2005, 04:13 PM #1
EXCEL question -- last entry in column
For Excell 2000 -- In a column e.g A1 to A5 - how can I get cell A6 to return the last entry in the column? Logically the formula would check the cells starting w/ A5 for "Emptyness" and would keep going sequentialy until it got to a cell that had either a text or numerical entry.

cat
dog
3
Bingo

Bingo -- cell A6 would return last entry -- "Bingo"

or

cat
dog
3

3 -- cell A6 would return last entry or "3"

Column lengh can be any number of cells long but each cell is filled sequentialy - A1 then A2 then A3 etc

My only success (limted) is w/ nested "IF/THEN" statments and then I was limted to a column of 5 entries --

Any suggestions would be greatly appreciated!!!!
 maxflia10
Member with 331 posts.

Join Date: Feb 2003
27-Jan-2005, 09:29 PM #2
For mixed data,

=INDEX(A:A,MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",60),A:A)))

=LASTROW(A:A)

For numeric data only

=LOOKUP(9.9999999999999+307,A:A)

for text data,

=INDEX(A:A,MATCH(REPT("z",90),A:A))
 KJS2
Junior Member with 4 posts.

Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 11:36 AM #3
Thank You!!!! -- I got the # only to work and the text only to work -- and I can get the number potion of the combined to work -- still havn't puzzled out the text potion of the combined -- will work more on it this PM.

Thanks again!
 KJS2
Junior Member with 4 posts.

Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 03:51 PM #4
Solved - Excell last entry
My problem was solved -- thank you MAXFLIA10 - whoever you are!!

A suggestion to any who might try to use this is to delete the 9.999999999E+307 and replace with a whole number that is greater than any number that may show up in the column -- in my case I used the # 10,000,000 -- and it workded great.

The only other problem I had is that all cells must be "clean" -- any cells "deleted" w/ the space bar will cause a "#N/A" to appear. As soon as I "cleaned them" w/ the right click "clear contents" the formula worked.

Thanks again.
 KJS2
Junior Member with 4 posts.

Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 03:57 PM #5
p.s. I will make a donation to the site -- saved me many hours!!!!
 maxflia10
Member with 331 posts.

Join Date: Feb 2003
29-Jan-2005, 01:42 AM #6
Quote:
 Originally Posted by KJS2 A suggestion to any who might try to use this is to delete the 9.999999999E+307 and replace with a whole number that is greater than any number that may show up in the column -- in my case I used the # 10,000,000 -- and it workded great. Thanks again.

You're short a few 9's,

=9.99999999999999E+307 is the biggest number Excel recognizes. You can also define the numbers and use the name in the formula.

=LOOKUP(NAME,A:A)

Same with the REPT("z",90)

BTW,

Last edited by maxflia10; 29-Jan-2005 at 01:52 AM..
 techguy.org/324036
As Seen On

WELCOME TO TECH SUPPORT GUY!

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

### Find the solution to your computer problem!

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)