Count delay of specific number after it finds reset to 0, and repeat counts

[motilulla]

Hello,

I need a formula or VBA, which can count delay of specific number after it finds reset to 0, and repeat counts

My numbers are in the B6 to down in the column I want to find the delay of particular of number "1" in the given example first delay of number "1" is find at 9 counting from cell B6 to down, once find place 9 in the column C in front of the number "1", reset count to "0" repeat the process keep counting delay till end of the column.

Also for more detail the image is attached.

*	A	B	C	D	E
1
2
3
4		Data	Delay
5		Data	Result
6		16
7		7
8		14
9		4
10		15
11		33
12		3
13		4
14		1	9
15		14
16		6
17		5
18		15
19		1	5
20		15
21		6
22		9
23		2
24		2
25		3
26		4
27		2
28		24
29		7
30		9
31		5
32		20
33		6
34		1	15
35		8
36		5
37		15
38		21
39		12
40		1	6
41		14
42		1	2
43		1	1
44		6
45		4
46		1	3
47		1	1
48		1	1
49		1	1
50		15
51		3
52		7
53		3
54		6
55		10
56		7
57		1	8
58		11

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2
3
4		Data	Delay
5		Data	Result
6		16
7		7
8		14
9		4
10		15
11		33
12		3
13		4
14		1	9
15		14
16		6
17		5
18		15
19		1	5
20		15
21		6
22		9
23		2
24		2
25		3
26		4
27		2
28		24
29		7
30		9
31		5
32		20
33		6
34		1	15
35		8
36		5
37		15
38		21
39		12
40		1	6
41		14
42		1	2
43		1	1
44		6
45		4
46		1	3
47		1	1
48		1	1
49		1	1
50		15
51		3
52		7
53		3
54		6
55		10
56		7
57		1	8
58		11
Lists

Cell Formulas
Range	Formula
C6:C58	C6	=IF(B6=1,ROW()-LOOKUP(2,1/((B$5:B5=1)+(B$5:B5="Data")),ROW(B$5:B5)),"")

 

[motilulla]

Fluff, it works as treat query solved.

I do appreciate your kind help and time you spent to solve my question.

Kind Regards

Moti

 

[Fluff]

Glad to help & thanks for the feedback.