Advanced LOOKUP: Return list of all positive/negative values

[cpis0002]

I have a large table, in this problem, I am using column B and column J rows 9 to 554.

I need a formula to give me a list of all the positive values, and a list of all the negative values... So it will find any value which is not equal to zero and give me the text of each one found in column B.

I would do it by creating a 2 columns next to the data which numbers the postive data in one column, and negative data in the next.
That is: cell K150 will have the formula:
=IF(J150>0,COUNT($K$5:$K149)+1,""))

and then a lookup formula to lookup values (1,2,3,etc) and return the data in column B... but this is alot of work since I have a large amount of columns (since I have other columns like column J which need the same process somewer else)...

 

[xenou]

All positive values in $B$9:$J$554 (assumption: cells have numeric values)
=CountIf($B$9:$J$554,">=0")

All negative values in $B$9:$J$554 (assumption: cells have numeric values)
=CountIf($B$9:$J$554,"<0")

To return data in column B in Column K
In cell K1, formula is
=B1
Then fill down.

I don't understand why you have given cell K150 as an example, or why you are counting only in cells $K$5:$K149, and adding one. Please try to explain again if you still need help (try to use precise cell references for everything -- not just "count values" but count values in these cells..., not just "lookup values" but lookup values in these cells..., and so on).

Regards