How to find first or last nonempty "valid" value in a range

[TomCon]

I want to find the first or last nonempty "valid" value in a range.

By "valid" value, i mean a numeric value, or a string.

So, in a range, if a cell is blank, has value "" [you can get it for instance with formula =IF(True,"","")], or has an error value, skip over it and keep looking.

So, for the following values {blank,ERR,"","red",55,blank,"",ERR}
Then
First nonempty = "red"
Last nonempty = 55

Anybody have a formula that will do this? (or could be separate formula for first value vs. last value). Or, at last resort, a VBA function that will return it. Getting either the value itself or the index would be ok.

Thanks much!
Tom

 

[Scott Huish]

You're not really using =IF(TRUE,"","") are you? ="" would do the same thing.

Are what you have marked as ERR error values or the actual text ERR?

 

[jasonb75]

Not quite sure if this is what you meant, and I'm sure there is a better way to do this that I'm missing.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>#N/A</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Red</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">55</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>#N/A</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">55</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=NA()</TD></TR><TR><TD>A3</TD><TD>=""</TD></TR><TR><TD>A7</TD><TD>=""</TD></TR><TR><TD>A8</TD><TD>=NA()</TD></TR><TR><TD>B10</TD><TD>{=INDEX($A$1:$A$8,MIN(IF(LEN(IF(ISERROR($A$1:$A$8)+ISBLANK($A$1:$A$8),"",$A$1:$A$8)),ROW($A$1:$A$8))))}</TD></TR><TR><TD>B11</TD><TD>{=INDEX($A$1:$A$8,MAX(IF(LEN(IF(ISERROR($A$1:$A$8)+ISBLANK($A$1:$A$8),"",$A$1:$A$8)),ROW($A$1:$A$8))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[Scott Huish]

Excel Workbook
	A	B	C	D
1
2	#DIV/0!		First	red
3	red		Last:	55
4	55
5
6
7	#DIV/0!
Sheet1

 

[TomCon]

YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o></o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o>
<o></o>
<o>Thanks! This is great!</o>
<o>Tom</o>
<o></o>

 

[Aladin Akyurek]

Re: YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o></o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o>
<o></o>
<o>Thanks! This is great!</o>
<o>Tom</o>
<o></o>

For a definite range as B21:B33...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#DIV/0!</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>red</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#N/A</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#N/A</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR></TBODY></TABLE>

where B21 houses a formula like ="" in order
to simulate a formula blank, we can also invoke:

Control+shift+enter, not just enter...

=INDEX(B21:B33,MATCH(1,SEARCH("?*",B21:B33),0))

which should yield the first non-blank entry;

And just enter...

=LOOKUP(9.99999999999999E+307,SEARCH("?*",B21:B33),B21:B33)

which should yield last non-blank entry.

For any reference, we would either a List (Excel 2003) or a Table (Excel 2010 and later)
or construct a dynamic named range in order to run the foregoing formulas.

 

[jasonb75]

Re: YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o></o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o>
<o></o>
<o>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o>
<o></o>
<o>Thanks! This is great!</o>
<o>Tom</o>
<o></o>

Aladins suggestion is far better, but for information, the trick here is omit the rows in the index array, using hotpepper's formula

<o>=INDEX(B:B,MIN(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33)))))</o>
<o></o>
<o>The rest of the array restricts the result to rows 21:33, so an incorrect result can never be returned regardless of what is in B1:B20, or B34 onwards.</o>