Returning the last value in a row

[Anthony Slater]

Hi

What formula do I need to return the last known value in a
row?

ie.
ROW 1 contains 5 values in COL a,b,c,d and e
ROW 2 contains 2 values in COL a, b is empty,c,d is empty,
e is empty
How can I return the last value in each respective ROW
(COL e and COL c)?

I think I need something to jump from one colum to the
next and stopping when it finds a value but still
continues to the end of the specific amount of columns
just in case there is another value

Hope I explained this well

 

[Frank Kabel]

Hi
if you have no blank columns in between use something like
=OFFSET($A$1,0,COUNTA(1:1)-1)
for row 1 (Note: enter this formula outside of row 1)

 

[Anthony Slater]

Thanks, that really helped me out

-----Original Message-----
Hi
if you have no blank columns in between use something like
=OFFSET($A$1,0,COUNTA(1:1)-1)
for row 1 (Note: enter this formula outside of row 1)

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Don Guillett]

From a posting by Frank

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))))

 

[Frank Kabel]

Hi Don
thanks for this repost
But Aladin pointed correctly out that instead of
REPT(CHAR(255),255)
one should use
REPT("z",255)

So below a corrected repost for finding the last value in columns or
rows (also corrected some missing parenthesis and wrong sheet
references) :
-------------------
A. Collection of formulas to return the last value in a COLUMN
depending on the type/structure of data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following depending of the
type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MATCH(REPT("z",255),A:A)
))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<>"",0,ROW(A:A))))

--

B. Collection of formulas to return the last value in a ROWdepending on
the type/structure of data.
1. If you have no blank columns in between use
=OFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following depending of
the type of values in your row:
2.a. If you have ONLY text values in column A try
=INDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=INDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=LOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1),MATCH(REPT("z",255),1:
1)))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(1:1,1,MAX(IF(1:1<>"",0,COLUMN(1:1))))