Searching a Row of Data and Returning a Value

[Stats]

I have tried all sorts (less macros which I am seeking to avoid) and
searched the web but to no avail!!

I am trying to use Excel to search a row of data that is interspersed
(i.e. not in a continuous range but instead for example B3, W3, AB3
etc) and may or may not be populated with text values. Having
searched the row I want to be able to select the latest text value
(i.e. the row represents a timeline of data). See example below:

A3 W3 AB3
Jim "" (i.e. blank returned by formula) John

Therefore I can see that AB3 is populated with text and is therefore
the latest entry and therefore the formula would return "John".

I would highlight that I do not know what value will be returned
therefore I cannot do a lookup/index.

Also I think I could use some form of COUNT function (require to be
able to count text but not blanks "" and also do it with interspersed
cells not a range) to establish the last text value and then use some
form of subtraction/addition to get to the correct cell but all the
COUNT functions seem to require a continuous range and not separate
values.

Please can anyone help???

Many thanks

 

[Stats]

I have tried all sorts (less macros which I am seeking to avoid) and
searched the web but to no avail!!

I am trying to use Excel to search a row of data that is interspersed
(i.e. not in a continuous range but instead for example B3, W3, AB3
etc) and may or may not be populated with text values.  Having
searched the row I want to be able to select the latest text value
(i.e. the row represents a timeline of data).  See example below:

A3             W3                                                 AB3
Jim             "" (i.e. blank returned by formula)     John

Therefore I can see that AB3 is populated with text and is therefore
the latest entry and therefore the formula would return "John".

I would highlight that I do not know what value will be returned
therefore I cannot do a lookup/index.

Also I think I could use some form of COUNT function (require to be
able to count text but not blanks "" and also do it with interspersed
cells not a range) to establish the last text value and then use some
form of subtraction/addition to get to the correct cell but all the
COUNT functions seem to require a continuous range and not separate
values.

Please can anyone help???

Many thanks

I would also state that I sought to use =IF(ISTEXT(etc) however, I
established that I am limited to only 7 IF statements and I require 14.

 

[Ron Rosenfeld]

I have tried all sorts (less macros which I am seeking to avoid) and
searched the web but to no avail!!

I am trying to use Excel to search a row of data that is interspersed
(i.e. not in a continuous range but instead for example B3, W3, AB3
etc) and may or may not be populated with text values. Having
searched the row I want to be able to select the latest text value
(i.e. the row represents a timeline of data). See example below:

A3 W3 AB3
Jim "" (i.e. blank returned by formula) John

Therefore I can see that AB3 is populated with text and is therefore
the latest entry and therefore the formula would return "John".

I would highlight that I do not know what value will be returned
therefore I cannot do a lookup/index.

Also I think I could use some form of COUNT function (require to be
able to count text but not blanks "" and also do it with interspersed
cells not a range) to establish the last text value and then use some
form of subtraction/addition to get to the correct cell but all the
COUNT functions seem to require a continuous range and not separate
values.

Please can anyone help???

Many thanks

=LOOKUP(2,1/(1-ISBLANK(3:3)),3:3)


--ron

 

[Ron Rosenfeld]

=LOOKUP(2,1/(1-ISBLANK(3:3)),3:3)


--ron

Note: the formula looks at the entire Row 3; you could make it more
restrictive, but make sure the array vector and result vector are identical.
--ron

 

[Stats]

=LOOKUP(2,1/(1-ISBLANK(3:3)),3:3)

--ron- Hide quoted text -

- Show quoted text -

Hi Ron,

Many thanks for replying however, does the formula you posted not
require a continuous range (i.e. 3:3) and therefore does not fit my
interspersed cell criteria. Also when I try your formula if I remove
the last value and the next value is "" (i.e. blank) it returns this
blank and does not move to the next text value?

Many thanks

 

[Stats]

Hi Ron,

Many thanks for replying however, does the formula you posted not
require a continuous range (i.e. 3:3) and therefore does not fit my
interspersed cell criteria.  Also when I try your formula if I remove
the last value and the next value is "" (i.e. blank) it returns this
blank and does not move to the next text value?

Many thanks- Hide quoted text -

- Show quoted text -

Just to clarify what I mean by interspersed
data........................other values between the actual values
that I want to check. This could be changed if there is no way of
selecting specific cell values (i.e. A3, W3, AB3 etc).

Many thanks

 

[Ron Rosenfeld]

Hi Ron,

Many thanks for replying however, does the formula you posted not
require a continuous range (i.e. 3:3) and therefore does not fit my
interspersed cell criteria. Also when I try your formula if I remove
the last value and the next value is "" (i.e. blank) it returns this
blank and does not move to the next text value?

Many thanks

The formula will work as posted, except that "" is NOT a blank, so it would
return it.

To modify it to "not" return the "", try this:

=LOOKUP(2,1/(1-(LEN(3:3)=0)),3:3)

And yes, you do have to specify a contiguous range of cells for this to work.

Do some of the interspersed cells have data that you need to ignore?
--ron

 

[Ron Rosenfeld]

Just to clarify what I mean by interspersed
data........................other values between the actual values
that I want to check. This could be changed if there is no way of
selecting specific cell values (i.e. A3, W3, AB3 etc).

Ah -- there may be a way of doing this with a worksheet function, but it'd be
much simpler (for me) to do this with a UDF, especially late at night.

<alt><F11> opens the VBEditor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use UDF, enter a formula of the type:

=LastEntry(rng) into some cell.

rng can either be a named, discontinuous (or continuous) range; OR you can
insert the individual cells into the argument, but the cells must be within a
parenthesis also (so you would see a doubled parenthesis). So if you did that,
you would use:

=LastEntry((A3,W3,AB3))

=================================
Option Explicit
Function LastEntry(rng As Range)
Dim c As Range
LastEntry = "No Entries" 'Error Message
For Each c In rng
If Len(c.Value) > 0 Then
LastEntry = c.Value
End If
Next c
End Function
==================================

--ron

 

[Stats]

Ah -- there may be a way of doing this with a worksheet function, but it'dbe
much simpler (for me) to do this with a UDF, especially late at night.

<alt><F11> opens the VBEditor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use UDF, enter a formula of the type:

=LastEntry(rng) into some cell.

rng can either be a named, discontinuous (or continuous) range; OR you can
insert the individual cells into the argument, but the cells must be within a
parenthesis also (so you would see a doubled parenthesis).  So if you did that,
you would use:

=LastEntry((A3,W3,AB3))

=================================
Option Explicit
Function LastEntry(rng As Range)
Dim c As Range
LastEntry = "No Entries" 'Error Message
For Each c In rng
    If Len(c.Value) > 0 Then
        LastEntry = c.Value
    End If
Next c
End Function
==================================

--ron

Hi Ron,

I am not familar with macros and am somewhat wary, particularly given
it is 4.15am (GMT) and I do not want to muck anything up.

I have used your code kindly provided - =LOOKUP(2,1/(1-(LEN(3:3)=0)),
3:3) and it works. Would be ideal to have a function to pick up
interspersed data but understood that it is difficult/challenging.

I would ask though that you please explain what exactly your formula
is doing i.e. LOOKUP(lookup_value,lookup_vector,result_vector) but
includes a LEN (character length check etc).

Many thanks

 

[Ron Rosenfeld]

Hi Ron,

I am not familar with macros and am somewhat wary, particularly given
it is 4.15am (GMT) and I do not want to muck anything up.

I have used your code kindly provided - =LOOKUP(2,1/(1-(LEN(3:3)=0)),
3:3) and it works. Would be ideal to have a function to pick up
interspersed data but understood that it is difficult/challenging.

I would ask though that you please explain what exactly your formula
is doing i.e. LOOKUP(lookup_value,lookup_vector,result_vector) but
includes a LEN (character length check etc).

Many thanks

I doubt you would "muck anything up", at least not irretrievably (back up your
data first), but I'm glad you got the worksheet function to work.

How does it work?

Let's Dissect:

LEN(3:3)=0 returns TRUE or FALSE for each cell in Row 3. This is similar to
ISBLANK(3:3) except it will also return FALSE if a formula in the cell is
returning ' "" '.

1-LEN(3:3)=0 coerces the TRUE FALSE return to a 0 or -1 so that when it is made
the denominator in the 1/(...), the TRUE's will give a DIV/0 error, and the
FALSE's will give a 1.

I had adapted this from the ISBLANK variation of this formula, late at night
without thinking. However, this part of the equation could (should?) be
simplified to: LEN(3:3) and no need to subtract.

So now we have the simplified version:

LEN(3:3) will return an array of values equal to the various lengths of the
strings in the cells. It might look something like
{1,0,1,0,7,0,2,0,1,1,5,1,0,0,0,0, ...}

Then, dividing that into 1

1/LEN(3:3) returns an array of either numbers, or DIV/0 errors.

{1,#DIV/0!,1,#DIV/0!,0.142857142857143,#DIV/0!,0.5,#DIV/0!,1,1,0.2,1,0.3,#DIV/0!,#DIV/0!,#DIV/0!,...}

Now you should look at HELP for the vector form of the LOOKUP function in order
to follow along.

If the vector form of the LOOKUP function is given a BIG NUMBER as an argument,
one that does not appear in the array, it will match with the LAST numeric
value in the array. LOOKUP will ignore error values. In an array constructed
as above, the last numeric value has to match with the last entry that has
LEN>0. All the other entries will return errors. Since we are dividing LEN
into "1", no value can be greater than 1; so 2 must be larger than any returned
value; meeting the conditions to match with the last numeric value.

We then return the corresponding value that is in result_vector which, since it
is the same dimension as lookup_vector, will be the actual value.

Going through this exercise, and having had a bit of sleep, allows me to
simplify the function a bit:

=LOOKUP(2,1/LEN(3:3),3:3)

should do the same thing as above.


--ron

 

[Stats]

I doubt you would "muck anything up", at least not irretrievably (back up yourdatafirst), but I'm glad you got the worksheet function to work.

How does it work?

Let's Dissect:

LEN(3:3)=0  returns TRUE or FALSE for each cell inRow3.  This is similar to
ISBLANK(3:3) except it will also return FALSE if a formula in the cell isreturning' "" '.

1-LEN(3:3)=0 coerces the TRUE FALSE return to a 0 or -1 so that when it is made
the denominator in the 1/(...), the TRUE's will give a DIV/0 error, and the
FALSE's will give a 1.

I had adapted this from the ISBLANK variation of this formula, late at night
without thinking.  However, this part of the equation could (should?) be
simplified to:  LEN(3:3) and no need to subtract.

So now we have the simplified version:

LEN(3:3) will return an array of values equal to the various lengths of the
strings in the cells. It might look something like
{1,0,1,0,7,0,2,0,1,1,5,1,0,0,0,0, ...}

Then, dividing that into 1

1/LEN(3:3) returns an array of either numbers, or DIV/0 errors.

{1,#DIV/0!,1,#DIV/0!,0.142857142857143,#DIV/0!,0.5,#DIV/0!,1,1,0.2,1,0.3,#D­IV/0!,#DIV/0!,#DIV/0!,...}

Now you should look at HELP for the vector form of the LOOKUP function in order
to follow along.

If the vector form of the LOOKUP function is given a BIG NUMBER as an argument,
one that does not appear in the array, it will match with the LAST numericvaluein the array.  LOOKUP will ignore error values.  In an array constructed
as above, the last numericvaluehas to match with the last entry that has
LEN>0.  All the other entries will return errors.  Since we are dividing LEN
into "1", novaluecan be greater than 1; so 2 must be larger than any returnedvalue; meeting the conditions to match with the last numericvalue.

We then return the correspondingvaluethat is in result_vector which, sinceit
is the same dimension as lookup_vector, will be the actualvalue.

Going through this exercise, and having had a bit of sleep, allows me to
simplify the function a bit:

=LOOKUP(2,1/LEN(3:3),3:3)

should do the same thing as above.

--ron

Many thanks again for taking the time to help Ron!!!!!!!!