String Formula

[James]

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(P,SMALL(IF($P$2:$P$100<>"",ROW($P$2:$P$100)),ROW(A1)))


If this post helps click Yes

 

[James]

A B
1 apples
2 apples bananas
3 pears
4
5 bananas
6
7 pears

Can you write an array formula to make the above work. I sorta understand
but not quite. THANX

 

[Jacob Skaria]

=INDEX(A:A,SMALL(IF($A$2:$A$100<>"",ROW($A$2:$A$100)),ROW(A1)))

Enter the formula using Ctrl+Shift+Enter; to return the 1st value in ColA.
Copy/drag the formula down to get the subsequent values

If this post helps click Yes

 

[T. Valko]

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)>COUNTA(A$1:A$7),"",INDEX(A:A,SMALL(IF(A$1:A$7<>"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[James]

Cool. That worked better than Jacob's. His did not input the first item in
the list.
Although yours gave me #NUM! after the last item. no biggie I guess.

Thanx

 

[T. Valko]

Although yours gave me #NUM! after the last item.
Hmmm...

The only way that's possible is if you have formulas in column A and some of
them return formula blanks "". To account for that change this portion:

COUNTA(A$1:A$7)

To:

COUNTIF(A$1:A$7,"?*")

 

[Jacob Skaria]

Thanks Biff.. (I missed the error handling part).

To handle numerics and text we can have the below...
=IF(ROW(A1)<=COUNTIF(A:A,"?*")+COUNT(A:A),INDEX(A:A,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(A1))),"")

One query: Is there any specific reason for using =ROWS(B$1:B1) instead of
ROW($B1)

James:

In the intial post you have mentioned as P2 and hence I have referenced only
from cell 2 in the formula...

If this post helps click Yes

 

[T. Valko]

Is there any specific reason for using =ROWS(B$1:B1)

instead of ROW($B1)

It's more robust and doesn't really "cost" anything efficiency-wise.

If you insert a new row 1 then:

ROW($B1) becomes ROW($B2) and now evaluates as 2.

ROWS(B$1:B1) becomes ROWS(B$2:B2) and *still* evaluates as 1.