String Formula

  • Thread starter Thread starter James
  • Start date Start date
J

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
 
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:P,SMALL(IF($P$2:$P$100<>"",ROW($P$2:$P$100)),ROW(A1)))


If this post helps click Yes
 
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
 
=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
 
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.
 
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
 
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,"?*")
 
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
 
Is there any specific reason for using =ROWS(B$1:B1)
instead of ROW($B1)
Click to expand...

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.
 
Back
Top