Excel 2007 - How to find last value or record in a row 3

[elfa]

a b c d e f g
1 2 4 5 6
2 3 5 2
3 2 3 5 4

We have above data on a much larger scale. In column G we need a formula that returns the last record in the row even though the records end in different columns.

The data number in the columns is high so we can't use an "if formula" (300 columns by 3000 lines approx)

Would appreciate your help to give us the type of formula or function we can use to get column h above to return the last record. (answers in this case are 6, 2 and 4)

Thanks very much

Elfa

 

[SkipVought]

Hi,

Only way I can figure. Paste this into a MODULE. Use like any ss formula. The reference is any cell in the row.

Function LastValue(rng As Range)
    LastValue = Cells(rng.Row, Columns.Count).End(xlToLeft).Value
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Are the values always single digits?

If so you could concatenate the range and find the rightmost character.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[xlhelp]

You can also use a formula. I have used columns A through F.

[blue]=INDEX(A2:F2,MATCH(9.99999999999999E+307,A2:F2))[/blue]

Unfortunately, I can't take credit for it.

A man has only two choices: He can be right or he can be happy.

 

[krispi]

Wow - XLHelp that's impressive. Wish I knew how it works!

 

[SkipVought]

That does it with NUMBERS, but what about non numeric characters ALSO...
[tt]
=INDEX(A2:F2,MAX(IF(ISNA(MATCH(9.99999999999999E+307,A2:F2)),0,MATCH(9.99999999999999E+307,A2:F2)),IF(ISNA(MATCH(CHAR(255),A2:F2)),0,MATCH(CHAR(255),A2:F2))))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

XLHelp,

I too wanted to know how that formula worked and here is my analysis. Let me know if I got anything wrong.

Match is finding the rightmost cell that is not blank as the closest match to 9.9999999999+307. This formula is exploiting the fact that the Cells being searched are supposed to be ranked in ascending order so the Rightmost cell is considered the closest match to the number provided.

Index is reporting the cell value found at the Match location.

Correct??

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

The key issue is that MATCH, using the default Match_type of 1, looks for "the largest value that is less than or equal to lookup_value."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Match is finding the rightmost cell that is not blank as the closest match to 9.9999999999+307. This formula is exploiting the fact that the Cells being searched are supposed to be ranked in ascending order so the Rightmost cell is considered the closest match to the number provided.

Bingo!!!

Basically, an exhaustive search, holding the last value found.

A man has only two choices: He can be right or he can be happy.

 

[xlhelp]

That does it with NUMBERS, but what about non numeric characters ALSO...

=INDEX(A2:F2,MAX(IF(ISNA(MATCH(9.99999999999999E+307,A2:F2)),0,MATCH(9.99999999999999E+307,A2:F2)),IF(ISNA(MATCH(CHAR(255),A2:F2)),0,MATCH(CHAR(255),A2:F2))))

OP only mentioned numbers, so I didn't bother with text. It's great that you mentioned it, so now it's here for anyone that needs it.


A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

Yes, xl, but I often say to myself, "Self, what if...?" And then stuff begins to happen.

YOU, fine sir, started the whole thing. So I commend your post. ==> [COLOR=purple white]*[/color]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}