List only cells with positive values

[tghcogo]

Hello all

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?

 

[T. Valko]

Hello all

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?

What version of Excel are you using?

 

[tghcogo]

Oops it's 2003

 

[T. Valko]

Oops it's 2003

OK, since you'll be adding data we need to create a dynamic named range.

Goto the menu Insert>Name>Define
Name: Values (or whatever name you want to use as long as it's a valid name!)
Refers to: =$B$2:INDEX($B$2:$B$100,COUNTA($A$2:$A$100))
Use a reasonable end of range that allows for expected future data addition.
OK out

I'm assuming that there are no empty/blanks cells within the data range.

Enter this formula in C1. This will return the count of values that are >0.

=COUNTIF(Values,">0")

Enter this array formula** in C2:

=IF(ROWS(C$2:C2)>C$1,"",INDEX(A:A,SMALL(IF(Values>0,ROW(Values)),ROWS(C$2:C2))))

** 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.

Copy down until you start getting blank cells then copy down additional cells to allow for future returns when new data is added to the data table. How many rows should you copy the formula to? I don't know, only you know how many returns are possible.

 

[tghcogo]

Thanks a bunch, it works, and I' m embedding it into a larger spreadsheet.

 

[T. Valko]

Thanks a bunch, it works, and I' m embedding it into a larger spreadsheet.

You're welcome. Thanks for the feedback!

 

[Chester21]

This works very well.... How would I make the corresponding values show in column D?
Column C now shows what values are positive, but could column D show what those values are?

Thanks,
Chester21