Returning last value of the continuous range

[pradhishnair]

can i get a little help with my problem;


i wish to get the last value of a continuous range

e.g.


column A column B

1 = last value of continuous range i.e 3

2

3

(Blank Cell)

2 = last value of continuous range i.e 4

3

4

(Blank Cell)

5 = last value of continuous range i.e 66

6

55

66

(Blank Cell)

 

[NARAYANK991]

Hi Pradish ,


I have not really understood your requirement ; what is the data in column A ? Can you paste about 10 rows of data from column A ? What is the requirement in column B ? What separates the continuous ranges , are there blank cells between the 1,2,3 and the 2,3,4 and between the 2,3,4 and the 5,6,55,66 ?


Narayan

 

[Luke M]

Returns the last value from the first continuous range:

=INDEX(A:A,MIN(IF(ISBLANK($A$1:$A$100),ROW($A$1:$A$100)))-1)


This is an array formula that needs to be confirmed using Ctrl+Shift+Enter.

 

[pradhishnair]

Column A consist of non continuous(Numeric/Non numeric) data. i want the continuous data in column A to be treated as different ranges. as in Column A "1,;2;3" are continuous range and i wish to return last value i.e. 3 in column B.

Likewise in column A '1;;2;3' and '2;3;4' is seperated by a blank cell hence '2;3;4' is to be treated as a seperate range and i need to return the last value this range i.e. 4 in the adjacent column. Hope the problem is clear to yous sir.

 

[pradhishnair]

@ luke,

The formula is not working. since it is taking the whole column A as the array it is only returning the first value of the range.

 

[pradhishnair]

To simplify my problem


I wish to return the last value just before the next blank value in the column.

 

[Luke M]

=INDEX(A:A,MAX(IF(NOT(ISBLANK($A$1:$A$100)),ROW($A$1:$A$100))))

 

[Luke M]

Or, a non-array formula:

=INDEX(A:A,MAX(MATCH(9E99,A:A),MATCH("ZZZZ",A:A)))

 

[pradhishnair]

Not working.. it is giving me the same last column value for every range.


iam uploading the excel sheet. plz help me with it..

 

[pradhishnair]

https://docs.google.com/spreadsheet/ccc?key=0AgDJ-TLtQDaydGQ0X1hjSGwxcEpEWHFfQUJacktralE

 

[pradhishnair]

Thnks luke the first formula with a little tweak worked for me. Thank a lot...

 

[Luke M]

Are you wanting this?

[pre]

A       B
1
2
3	3

4
5
6	6
or just this?

A       B
1
2
3	

4
5
6	6

[/pre]

 

[bobhc]

Good day

I have used this and it worked for me....=IF(A11="",A10,"")

 

[Montrey]

How about an example workbook so we can all stop fumbling around! :D

 

[Luke M]

@Montrey

See OP's post above

http://chandoo.org/forums/topic/returning-last-value-of-the-continuous-range#post-24583

 

[Luke M]

And now I see later that issue has been solved.

http://chandoo.org/forums/topic/returning-last-value-of-the-continuous-range#post-24584

 

[Montrey]

@Luke M


Oops missed that! Also missed that he found the answer. haha.

/thread Completed