Unique formula which can find delay for number 1 to 50 at once

[motilulla]

Using Excel 2010
Hello,

I am using following formula given by Fluff under this link Count delay of specific number after it finds reset to 0, and repeat counts

I have a few numbers of results in the columns “B” they are in between 1 to 50, In the column “C” there is formula which finds the delay of the number is in the cell “C5” to get the delay of the each numbers I have to change the number and put delay of their corresponding number in the columns “G:M”

Now I require a formula which find delay of each number automatically in the columns “G:M” in the cells G6:M55 as shown in the example below. (Also I want to mention that appearance for the each number max will be 10 times)

Does it is possible to have unique formula which can find the delay of each numbers?

Note: my request if I could be possible, I can use the given formula in the excel 2000 also.

MrExcel Question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4		Few Result	Delay		Unique	Unique	1 Time	2 Time	3 Time	4 Time	5 Time	6 Time	7 Time
5		Few Result	1		Num	Count	Delay	Delay	Delay	Delay	Delay	Delay	Delay
6		29			1	3	16	14	58
7		32			2	1	75
8		49			3	2	25	53
9		45			4	3	48	1	18
10		42			5	0	98
11		7			6	5	39	17	4	5	28	21
12		15			7	3	37	4	50	5
13		36			8	3
14		8			9	2
15		35			10	3
16		12			11	1
17		34			12	2
18		21			13	2
19		9			14	0
20		33			15	2
21		1	16		16	0
22		50			17	0
23		10			18	5
24		13			19	0
25		20			20	2
26		23			21	2
27		26			22	1
28		35			23	2
29		24			24	2
30		3			25	0
31		10			26	2
32		30			27	0
33		33			28	0
34		39			29	2
35		1	14		30	4
36		40			31	1
37		15			32	2
38		35			33	4
39		23			34	1
40		30			35	3
41		30			36	2
42		8			37	2
43		45			38	1
44		6			39	1
45		40			40	4
46		31			41	1
47		44			42	1
48		7			43	2
49		33			44	2
50		29			45	2
51		49			46	1
52		7			47	1
53		4			48	2
54		4			49	5
55		40			50	2
56		40
57		18
58		12
59		18
60		43
61		6
62		11
63		20
64		24
65		6
66		8
67		49
68		48
69		37
70		6
71		50
72		4
73		18
74		48
75		38
76		26
77		21
78		22
79		49
80		2
81		30
82		47
83		3
84		41
85		44
86		49
87		10
88		32
89		43
90		33
91		9
92		13
93		1	58
94		37
95		36
96		46
97		18
98		6
99		18
Sheet1

Cell Formulas
Range	Formula
G6	G6	=MATCH($E6,$B$6:$B$99,0)
F6:F55	F6	=COUNTIF($B$6:$B$99,E6)
C6:C99	C6	=IF($B6=C$5,ROW()-LOOKUP(2,1/(($B$5:$B5=C$5)+($B$5:$B5="Few Result")),ROW($B$5:$B5)),"")

Please help

Thank you all.

Regards,
Moti

 

[motilulla]

motilulla said:
I modified the previous formula but which were not resulting correctly with “0”

I don't know what you mean by that.

Peter, I apologise it is my mistake; I just forgot to erase this line while posting, from the message all worked perfect!

Good Luck!

Kind Regards,
Moti

 

[Peter_SSs]

OK, thanks for clarifying.