How to retrieve the last "Text" value in a column

  • Thread starter Thread starter Mike Krumel
  • Start date Start date
M

Mike Krumel

Hey Gang,
I have a function that does a super job of
getting the last value in a column for times and other
numeric values, but it does not work with persons names.
Can this function be modified for names or do I need to go
a different route?

=OFFSET(X8,MATCH(MAX(X8:X4002)+1,X8:X4002,1)-1,0)

Thanks, Mike


Windows 2000
Excel 2000
 
Hi Mike
a repost for getting the last value in a range (adapt to your needs)

------

if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(Entry!A:A,MATCH(9.99999999999999E307,A:A))

3. If you have both (text and values)
=INDEX(Entry!A:A,MAX(MATCH(9.99999999999999E307,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))
 
Mike,

Try the following array formula:
=OFFSET($C$4,MAX(($C$4:$C$10<>"")*ROW($C$4:$C$10))-ROW($C$4),0)

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
enclose the formula enclosed in curly braces {}.

Change the cell references as required.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))

should be:

=INDEX(Entry!A:A,MATCH(REPT("z",255),Entry!A:A))

or

=LOOKUP(REPT("z",255),Entry!A:A)
 
Thanks Chip, works fine.

Mike
-----Original Message-----
Mike,

Try the following array formula:
=OFFSET($C$4,MAX(($C$4:$C$10<>"")*ROW($C$4:$C$10))-ROW ($C$4),0)

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
enclose the formula enclosed in curly braces {}.

Change the cell references as required.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





message news:[email protected]...


.
Click to expand...
 
Back
Top