IF - THEN - OTHERWISE -......UNLESS! ???

[dim]

Hi, this one is a doosie! If anyone can help it'd be really great.

I have three columns of data....A1:A10, B1:B10 and C1:C5.

Column A is Numeric Data in each cell, Column B contains text in each cell,
and I want my function to work on column C.

I want the fuction to check down along column A, from A1, until it finds a
value greater than zero, at which time it will copy that A cell's
corresponding B cell into C1. (e.g: =IF(A1>0,B1) ) However, if at any time a
value is entered into C1, then I want the function to copy the next value it
finds into C2 instead, and so on into C3 etc.

With this sheet, column B has about 100 text entries, but only 10 or so of
those entries corresponding numbers in column A will be greater than 0. I
want all those text entries organised at the top of column C for easy
referance, so putting a seperate function into each of 100 column C cells is
not an option.

Does anyone have any ideas?

 

[Bob Phillips]

Put this in C1 and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$10>0,ROW($A$1:$A$10)),ROW($A1))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10>0,ROW($A$1:$A$10)),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dim]

Thanks Bob but I've tried that and it doesn't exactly do what I want.

Your formula takes the first value greater than zero that it finds, shows
the corresponding text for that cell, and then it stops.

After it shows the text for cell B1 because A1 was greater than zero, I need
it to keep moving down the A column, and then the next time it finds a value
greater than zero, to show that corresponding text in cell C2, then keep
checking down and show the next one in cell C3 and so on. So at the end of it
checking hundreds of values in column A, I might have seven or eight (Or
three, or fifty etc) cells at the top of column C filled in with text from
column B.

I hope that makes sense.....any ideas?

 

[Don Guillett]

Sub findgreaterthan()
J = 1
For i = 1 To 100
If IsNumeric(Cells(i, 1)) And Cells(i, 1) > 0 Then
Cells(J, 3) = Cells(i, 2)
J = J + 1
End If
Next
End Sub

 

[Max]

Believe Bob's array works, but you probably didn't array-enter it correctly.
Re-click inside the formula bar for C1, then press CTRL+SHIFT+ENTER (CSE) to
confirm the formula. You should see curly braces { } wrapped around the
formula by Excel (in the formula bar). If you don't see the curlies, then it
hasn't been array-entered properly. You gotta try the CSE again. Once
correctly array-entered, then just copy C1 down to C10.

Anyway, here's an alternative play which also achieves it using non-array
formulas
In C1: =IF(ROW()>COUNT(D),"",INDEX(B:B,SMALL(D,ROW())))
In D1: =IF(A1="","",IF(A1>0,ROW(),""))
Select C11, copy down to cover the max expected extent of data in col A,
say down to D100? Hide away col D. Col C will return the required results
from col B, all neatly bunched at the top.

 

[dim]

Thanks Don and Max,

I didn't realise there could be so many ways to achieve the same result!

I read your two posts, and when Max said that Bob's array works, I went back
and had a look at it. I had it working fine (I thought) but it just wasn't
doing what I wanted. Anyway, I realised what the problem was....I entered his
array in C1, but didn't realise I had to copy it down into C2,C3 etc. Oops!

So I have a way to achieve what I wanted, but now Im going to try out your
two ways to see how they work aswell. Thanks again.

 

[Don Guillett]

The macro approach is much less overhead for your file

 

[Bob Phillips]

But it's a macro, so it doesn't recalculate if the data changes.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Guillett]

could

 

[dim]

Hi again,

I was using Bob's array formula fine in my test sheet, but now I've opened a
new file and copied it acorss. I changed some of the values to referance the
correct cells, and pressed ctrl-shift-enter to activate it, but its not
working!!! :-(

This is what I have in the cell and dragged down to the cells below:

{=IF(ISERROR(SMALL(IF($A$6:$A$15>0,ROW($A$6:$A$15)),ROW($A6))),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$15>0,ROW($A$6:$A$15)),ROW($A6))))}

Whats wrong?

 

[Bob Phillips]

dim,

my formula was specific to the data being in A1 etc. To be more versatile,
use

=IF(ISERROR(SMALL(IF($A$6:$A$15>0,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1),"",INDEX($B$6:$B$15,SMALL(IF($A$6:$A$15>0,ROW($A$6:$A$15)),ROW($A6)-MIN(ROW($A$6:$A$15))+1)-MIN(ROW($A$6:$A$15))+1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dim]

GREAT! :-D Thanks a million Bob, that's working perfect now.