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

 

[HongRu]

Try.

G6

=IFERROR(MATCH($E6,$B$6:$B$99,0),"")

copy downward to the last row.

H6

=IFERROR(MATCH($E6,OFFSET($B$6:$B$99,SUM($G6:G6),),0),"")

copy to right and down

 

[motilulla]

Try.

G6

=IFERROR(MATCH($E6,$B$6:$B$99,0),"")

copy downward to the last row.

H6

=IFERROR(MATCH($E6,OFFSET($B$6:$B$99,SUM($G6:G6),),0),"")

copy to right and down

HongRu, Spot on! Formulas worked like a charm, as I request perfect, also it worked fine with Excel v2000.

Thank you so much for resolving it.

Have a great weekend, good luck!

Kind Regards,
Moti

 

[HongRu]

Glad to help.

 

[Peter_SSs]

If you wanted to use a single formula and also avoid using the volatile function OFFSET, you could try this array-entered formula.

22 12 24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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
11	7			6	5	39	17	4	5	28
12	15			7	3	6	37	4
13	36			8	3	9	28	24
14	8			9	2	14	72
Delay

Cell Formulas
Range	Formula
F6:F14	F6	=COUNTIF($B$6:$B$99,E6)
G6:M14	G6	=IFERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[motilulla]

HongRu, Spot on! Formulas worked like a charm, as I request perfect, also it worked fine with Excel v2000.

Thank you so much for resolving it.

Have a great weekend, good luck!

Kind Regards,
Moti

HongRu, your given formula worked perfect with excel 2010, but changed a bit as below to work with both excel 2010 & excel 2000 versions.

Have a great weekend, good luck!

MrExcel Question.xls
	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
11		7			6	5	39	17	4	5	28
12		15			7	3	6	37	4
13		36			8	3	9	28	24
14		8			9	2	14	72
15		35			10	3	18	8	56
16		12			11	1	57
17		34			12	2	11	42
18		21			13	2	19	68
19		9			14	0
20		33			15	2	7	25
21		1	16		16	0
22		50			17	0
23		10			18	5	52	2	14	24	2
24		13			19	0
25		20			20	2	20	38
26		23			21	2	13	59
27		26			22	1	73
28		35			23	2	21	13
29		24			24	2	24	35
30		3			25	0
31		10			26	2	22	49
32		30			27	0
33		33			28	0
34		39			29	2	1	44
35		1	14		30	4	27	8	1	40
36		40			31	1	41
37		15			32	2	2	81
38		35			33	4	15	13	16	41
39		23			34	1	12
40		30			35	3	10	13	10
41		30			36	2	8	82
42		8			37	2	64	25
43		45			38	1	70
44		6			39	1	29
45		40			40	4	31	9	10	1
46		31			41	1	79
47		44			42	1	5
48		7			43	2	55	29
49		33			44	2	42	38
50		29			45	2	4	34
51		49			46	1	91
52		7			47	1	77
53		4			48	2	63	6
54		4			49	5	3	43	16	12	7
55		40			50	2	17	49
Sheet1

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

Kind Regards,
Moti

 

[motilulla]

If you wanted to use a single formula and also avoid using the volatile function OFFSET, you could try this array-entered formula.

22 12 24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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
11	7			6	5	39	17	4	5	28
12	15			7	3	6	37	4
13	36			8	3	9	28	24
14	8			9	2	14	72
Delay

Cell Formulas
Range	Formula
F6:F14	F6	=COUNTIF($B$6:$B$99,E6)
G6:M14	G6	=IFERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Peter, I like your single formula worked perfect with excel 2010, will go with this one, but changed a bit as below to work with both excel 2010 & excel 2000 versions.

Thank you so much for giving an alternative single formula.

Have a great weekend, good luck!

MrExcel Question.xls
	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
11		7			6	5	39	17	4	5	28
12		15			7	3	6	37	4
13		36			8	3	9	28	24
14		8			9	2	14	72
15		35			10	3	18	8	56
16		12			11	1	57
17		34			12	2	11	42
18		21			13	2	19	68
19		9			14	0
20		33			15	2	7	25
21		1	16		16	0
22		50			17	0
23		10			18	5	52	2	14	24	2
24		13			19	0
25		20			20	2	20	38
26		23			21	2	13	59
27		26			22	1	73
28		35			23	2	21	13
29		24			24	2	24	35
30		3			25	0
31		10			26	2	22	49
32		30			27	0
33		33			28	0
34		39			29	2	1	44
35		1	14		30	4	27	8	1	40
36		40			31	1	41
37		15			32	2	2	81
38		35			33	4	15	13	16	41
39		23			34	1	12
40		30			35	3	10	13	10
41		30			36	2	8	82
42		8			37	2	64	25
43		45			38	1	70
44		6			39	1	29
45		40			40	4	31	9	10	1
46		31			41	1	79
47		44			42	1	5
48		7			43	2	55	29
49		33			44	2	42	38
50		29			45	2	4	34
51		49			46	1	91
52		7			47	1	77
53		4			48	2	63	6
54		4			49	5	3	43	16	12	7
55		40			50	2	17	49
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
Sheet2

Cell Formulas
Range	Formula
G6:M6	G6	=IF(ISERROR(SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G6:G55))-SUM($F6:F6)+$F6),"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G6:G55))-SUM($F6:F6)+$F6)
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)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Kind Regards,
Moti

 

[Peter_SSs]

changed a bit as below to work with both excel 2010 & excel 2000 versions.

Ah, yes, I forgot about IFERROR not going back that far. You could try this much shorter one instead if you want.

22 12 24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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
11	7			6	5	39	17	4	5	28
12	15			7	3	6	37	4
13	36			8	3	9	28	24
14	8			9	2	14	72
Delay

Cell Formulas
Range	Formula
F6:F14	F6	=COUNTIF($B$6:$B$99,E6)
G6:M14	G6	=IF(COLUMNS($G:G)>$F6,"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[motilulla]

Ah, yes, I forgot about IFERROR not going back that far. You could try this much shorter one instead if you want.

22 12 24.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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
11	7			6	5	39	17	4	5	28
12	15			7	3	6	37	4
13	36			8	3	9	28	24
14	8			9	2	14	72
Delay

Cell Formulas
Range	Formula
F6:F14	F6	=COUNTIF($B$6:$B$99,E6)
G6:M14	G6	=IF(COLUMNS($G:G)>$F6,"",SMALL(IF($B$6:$B$99=$E6,ROW($B$6:$B$99)-ROW($B$5)),COLUMNS($G:G))-SUM($F6:F6)+$F6)
Press CTRL+SHIFT+ENTER to enter array formulas.

Peter, I just replace your new shorter formula it worked even great with value “0” fantastic! I modified the previous formula but which were not resulting correctly with “0” with this one all is fixed ok!

Thank you so much for your kind help!

I want to wish you a Merry Christmas

Kind Regards,
Moti

 

[Peter_SSs]

I just replace your new shorter formula it worked even great with value “0” fantastic!

Good news!

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

I don't know what you mean by that.