# [Solved] Formulas: List of positive values

• Hi all,

I'm looking for a set of formula to reduce a list of values to a list of values greater than zero.
e.g.

Code
``````Orig    New
1    1
3    3
0    9
9    6
6    2
0    0 ( or NA() or "")
2    0 ( or NA() or "")``````

Ideally the reduced list would be in the same order as the original.
VBA is not an option.

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Hi Andy,

Does it have to be via formulas?

Just wondering whether the AutoFilter is an option?

Or, assuming the data starts at A1, enter in B1 =IF(A1&gt;0,1,""), then sort by column B.

Any use?

• I'm thinking similarly to Richie, but wonder if a pivot table is an option. The attached creates an ordinal list of the original numbers and uses this in a pivot table after IF&gt;0 filtering to create the new filtered list in column B of the pivot table.

Is this of any use?

• Hi Richie,

Edit: and thomach,

No I'm really looking for a formula only solution.

If I have to resort to auto filters, sorting or any other multi step solution I may as well go the vba Route.

I already have a udf that appears to work.

Thanks anyway.

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Hi Andy, I've used formulas only but needed 2 columns. It would be nice to get a 1 column solution. The key seems to be to pre-determine the # of zeros.

If your data is in a1:a7:
put this in b1 and copy down:
=MATCH(SMALL(A\$1:A\$7,COUNTIF(A\$1:A\$7,0)+ROWS(A\$1:A1)),A\$1:A\$7,0)
and put this in c1 and copy down:
=INDEX(A\$1:A\$7,SMALL(OFFSET(B\$1,0,0,COUNTIF(A\$1:A\$7,"&gt;0"),1),ROWS(A\$1:A1)),1)

I've got to learn to understand the MATCH function, so I may have done something stupid. Anyway, when I tried your formulas against the dataset I'd created and it parses the list correctly, but the ordering is lost. Should it have been sustained?

See the attached.

• Thanks Thomach, back to the drawing board and no soup for me.
It does keep the order but groups all duplicates together. :mad:
ie
2
0
4
3
5
2
becomes
2
2
4
3
5

• Hi guys,

Yep, the MATCH/INDEX combination works until you present it with duplicate values.

Unfortunately I do need to be able to identify which value is which.

At least this is not a simple question.
I was half expecting my question to give me one of those DOH! moments ;;)

Cheers
Andy

[h4]Cheers
Andy
[/h4]

• Ok, lets try again, this should work for integers but the approach could be modified to account for decimals.

data in column A

b1:
=A1+(ROWS(A1:A\$1)/1000*(A1<>0))
c1:
=MATCH(SMALL(B:B,COUNTIF(B:B,0)+ROWS(B\$1:B1)),B:B,0)
d1:
=INT(INDEX(B:B,SMALL(OFFSET(C\$1,0,0,COUNTIF(B:B,"&gt;0"),1),ROWS(B\$1:B1)),1))

Exactly what I needed.

The attached, auto sizing pie chart, will show you how I was intending to use this.

Thank you all for your efforts.

Cheers
Andy

[h4]Cheers
Andy
[/h4]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!