Display values only of the first X Occurences of a condition in a small range .

[Sharky007]

Hi...

Could anyone help me with a solution to this.

i am wondering if there is a function that will help me find... say 1st/2nd etc occurences of a condition in a list ?

e.g.

I want to pick out the first 3 numbers (say) that are over zero.

so if my list is 0.00, 23.45, 45.67, 0.00, 0.00, 78.90, 98.76 then I want ONLY 23.45, 45.67 and 78.90. (the first 3 numbers that are over zero only)

if my list is 12.34, 0.00, 0.00, 56.78, 0.00 ,0.00 ,0.00 .... then I only have 12.34, 56.78 and then a 0.00.

My actual aim and therefore next step would be to determine the position of those found values in their original lists, but I can do that using the MATCH function.

I mention both steps as ideally if I can get the positions in the lists without the middle step then great.

Any clues even would be a massive help as I seem to have gone around in circles with this one.

Thanks in advance.


Dave (Sharky007)

 

[Fluff]

Hi & welcome to MrExcel
How about

Book1
	B	C	D
2	0.00		3
3	23.45		4
4	45.67		9
5	0.00
6	0.00
7	0.00
8	0.00
9	78.90
10	98.76
Roster

Cell Formulas
Range	Formula
D2	{=MATCH(SMALL(IF($B$2:$B$10>0,$B$2:$B$10),ROWS($1:1)),$B$2:$B$10,0)+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sharky007]

Thank you so much for the quick reply.

I will try immediately I get home.

Thanks again.