• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning last value of the continuous range

pradhishnair

New Member
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)
 
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
 
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.
 
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.
 
@ 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.
 
Not working.. it is giving me the same last column value for every range.


iam uploading the excel sheet. plz help me with it..
 
Are you wanting this?

[pre]
Code:
A       B
1
2
3	3

4
5
6	6
or just this?

A       B
1
2
3	

4
5
6	6
[/pre]
 
@Montrey

See OP's post above

http://chandoo.org/forums/topic/returning-last-value-of-the-continuous-range#post-24583
 
And now I see later that issue has been solved. O.o

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