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.
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?
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
Public 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
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:
and put this in c1 and copy down:
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:
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 ;;)
Ok, lets try again, this should work for integers but the approach could be modified to account for decimals.
data in column A
: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.