return only positive values from Excel list

[RodG]

I have a list of vendor numbers and a list of option numbers that wer
assigned to that vendor. I have put in a formula that will lookup al
vendor numbers and return a list of the option numbers that apply t
that vendor or a -1 in the list that does not apply.
Example:
Vendor Option #
123 234
0 -1
0 -1
0 -1
123 456
0 -1
123 567

The list is very long. I am trying to write a formula that wil
condense the list to just the Option numbers that apply. (the positiv
numbers). I have tried the following code which I found in a Exce
Book:

=INDEX(Option,SMALL(IF(Option>0,ROW(INDIRECT("1:"&ROWS(Option)))),ROW(INDIRECT("1:"&ROWS(Option)))))

It returns negative numbers or the wrong numbers. I need a formul
that will get this list down to just the option numbers.

Thank you in Advance.

Ro

 

[Peo Sjoblom]

Why a formula, easiest way would be to use a filter, either autofilter or
the advanced filter

autofilter, filter on 123 and in the option column filter custom greater
than 0
then you can just select the visible cells and copy and paste them to
another sheet
to get your list..

 

[RodG]

Thanks for your reply Peo!

The reason I am looking for a formula is because when I import th
data, there will be around 40 different vendors with numerous option
for each. I will need to generate a work release (order form) listin
those options for each one. Additinally, this data will be importe
and the work releases generated by one of my accounts people.
Therefore, I want these lists to be generated automatically after th
data is imported so all that we have to do is print the work releases.
Can I do that with an autofilter? If so, how do I go about setting i
up?

Thanks again Peo,

Ro