[Solved] Formulas: List of positive values
 Andy Pope
 Closed



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>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>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.
Code
Display MorePublic Function udfFirstActive(MyList As Range, Position As Long) As Variant ' ' Return First value greater than zero from MyList ' Dim lngIndex As Long Dim lngCount As Long For lngIndex = 1 To MyList.Rows.Count If MyList.Cells(lngIndex, 1).Value > 0 Then lngCount = lngCount + 1 End If If lngCount = Position Then udfFirstActive = MyList.Cells(lngIndex, 1).Value Exit Function End If Next udfFirstActive = 0 End Function
Thanks anyway.
Cheers
Andy 
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 predetermine 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,">0"),1),ROWS(A$1:A1)),1) 

Egad,
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 
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,">0"),1),ROWS(B$1:B1)),1)):banghead:

:congrats: Bravo Egad :congrats:
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 
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!