Identifying the right-most value in an array (value not = 0)

[Vikas02d]

Hi, I am trying the following and seek some help.

I have an array that looks something like:

A____ | B_ | C_ | D_ | E_ | F_ |
ID-52 | 80 | 90 | "" | 70 | "" |
ID-53 | 45 | "" | "" | "" | 43 |
ID-54 | "" | "" | 67 | "" | "" |
ID-54 | "" | 88 | 88 | "" | 88 |


I am looking for a formula that will help me identify the right-most value in each row that is not a zero or "" (null).

So the result that I am expecting in the above data is:

for ID-52 >> 70
for ID-53 >> 43
for ID-54 >> 67
for ID-54 >> 88

Thanks for the help.
Vikas

 

[ssingh75]

What is your Column range limit?

Hope below one will help.....
<TABLE style="WIDTH: 32pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=43><COLGROUP><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=43>ID-52:</TD></TR></TBODY></TABLE>=RIGHT(VALUE(CONCATENATE(K2,L2,M2,N2,O2)),2)

Susheel
Excel,Access - 2007

 

[Vikas02d]

My Array will eventually be to the tune of about 1000 rows with about 150-200 coumns.

Regards,
Vikas

 

[Aladin Akyurek]

Hi, I am trying the following and seek some help.

I have an array that looks something like:

A____ | B_ | C_ | D_ | E_ | F_ |
ID-52 | 80 | 90 | "" | 70 | "" |
ID-53 | 45 | "" | "" | "" | 43 |
ID-54 | "" | "" | 67 | "" | "" |
ID-54 | "" | 88 | 88 | "" | 88 |


I am looking for a formula that will help me identify the right-most value in each row that is not a zero or "" (null).

So the result that I am expecting in the above data is:

for ID-52 >> 70
for ID-53 >> 43
for ID-54 >> 67
for ID-54 >> 88

Thanks for the help.
Vikas

If there is no real 0's...

G2, copied down:

=LOOKUP(9.99999999999999E+307,B2:F2)

If 0's must occur and they should be ignored...

G2, copied down:

=LOOKUP(9.99999999999999E+307,1/B2:F2,B2:F2)

 

[Vikas02d]

Tested. Successful. Many Thanks .

Best Regards
Vikas

 

[DaWookie]

All credit to Matthew Patrick and his submission here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=817

I merely revised it for your use.

Function GetRight(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)
     
    Dim CLL As Range
     
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            GetRight = GetRight & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
     
    GetRight = Right$(GetRight, 2)
     
End Function

So in your example, in Cell G2 you would enter =GetRight(B2:F2) and it will return 70, the right-most, non-blank, non-zero instance.

 

[Aladin Akyurek]

Tested. Successful. Many Thanks .

Best Regards
Vikas

You are welcome. Thanks for providing feedback.