HELP: Advanced lookup - return list of all postive/negative

[cpis0002]

I have column C which is basically empty, apart from a few values (500 rows but only 10-15 rows with data), some of the numbers in column C are negative, and others are positive.


I need it 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 A.

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 D150 will have the formula:
=IF(C150>0,COUNT($D$5:$D149)+1,""))

and then a lookup formula to lookup values (1,2,3,etc) and return the data in column A... but this is alot of work since I have a large amount of columns...

If you can come up with a better idea, please let me know.
Thanks again

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

sub list()
b = 1
c = 1
x = Cells(Rows.Count, 3).End(xlUp).Row
For a = 1 To x
If Cells(a, 3) <> 0 Then
If Cells(a, 3) = Abs(Cells(a, 3)) Then
Cells(b, 4) = Cells(a, 3) & " and " & Cells(a, 1)
b = b + 1
Else
Cells(c, 5) = Cells(a, 3) & " and " & Cells(a, 1)
c = c + 1
End If
End If
Next a
end sub
run the macro. it will list all +ves in col D and negatives in col E along with values of col A
Ravi