Count number of times a value occurs in the next 10 cells after another value

[Tao1123]

I have a list of numbers and i am trying to build a grid that shows the number of times a value occurs after each number but only in the next 10 cells. I am at a loss as where to even start. It is a running list and i would like to do it for each number in the list. Example:
List: 1,2,7,4,5,6,6,8,9,0
2 occurs after 1 once
7 occurs after 1 once
6 occurs after 1 twice
4 occurs after 2 once
So on and so forth except it would be built in a grid and for a few thousand numbers ranging from 0 to 50

 

[Zot]

I think helpers here need to understand more on the list you talked about. How your list looks like.
Is it everything 1,2,7,4,5,6,6,8,9,0 in cell A1 or listed down in rows A1, A2, A3, A4, etc or listed in A1, B1, C1, etc?

Why not use XL2BB to capture your sheet sample?

 

[Tao1123]

Each cell contains a single number going down column A so A1, A2, A3, etc. I am unfamiliar with XL2BB. Does it work with google sheets? I can post an example but all i have available right bow is google sheets. Excel is at the office.

 

[mikerickson]

If your list is in A1:A10, then INDEX($A$1:$A$10,MATCH(3, $A$1:$A$10, 0),1) is the first cell in that range that contains a 3

INDEX($A$1:$A$10,MATCH(3, $A$1:$A$10, 0)) : $A$10 is the range from that cell to A10

=COUNTIF(INDEX($A$1:$A410,MATCH(3, $A$1:$A$10, 0)) : $A$10, 2) is the number of 2's in that range, i.e. the number of 2's that come after 3.

Change the 3 and the 2 as desired.

 

[Tao1123]

Is there a way to do that with longer sets and identify each individual occurrence then aggregate the data in the grid? I have attached the sheet I am working with. Green means I have catalogued it in the grid. The y-axis on the grid is the number and the x-axis represents another number appearing within 10 cells after that occurrence.

November 5 2020 Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Set 1	Set 2		Number of Occurences		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36
2	17	4		5	0	1	2	1	2	3	1		1	1	3	4		1	2	2	1		1	3	1			1	1		1	1	3	1	1	3	3	1	2		1	1	50
3	21	2		5	1		1		1	1		3	1	1	1	1			5	5	2	2	3	1	4		1	1	2	2	1	2	1	2	1	1	3				1		50
4	16	24		2	2	1	1			1	1				1	2			2	2	1		1		2				1	1							1	1			1		20
5	13	32		3	3	1			2		1			2	2			1			2			2	1		1				1		4		3	4		1	1			1	30
6	16	1		3	4	1	1	1	1		1				1	3			2	1	2		2	2				1	1	1			2				1	1	2		1	2	30
7	28	31		4	5	2	3		2	1		1		3	3	2			1	1	1	1	1	2		2		1			1	1	1		1	2	2	2	1		2		40
8	0	13		3	6	1		2					1	1		1	1			3		1	1	1	3	1	1	1		3				1				3	2		1	1	30
9	13	14		3	7	1	1			2	2	1		3	2				1		1	1		2		1		2			1		1				3	1	3			1	30
10	7	23		5	8			1	1		1	4		3	1		1	1		3	1	3		4	3	2	1	1		3		1	1	1	1	1	1	5	2		2	1	50
11	33	15		3	9	2	2			1	1				1	3		1	1		1		1	2	1	1		1		1	1	1	2			1	1		2	1	1		30
12	18	13		5	10	1	6	1			1	2		1		4		1	1	5	4		3	2	3		1				3	2		1	1	1			3		3		50
13	31	19		1	11		1					1		1		1											1					2		1	1				1				10
14	25	30		2	12	1		1		1	2		1		2	2				1					2	1		1		1	1		1			1				1			20
15	4	10		5	13	2				2		1	3	1		2		1	2	6	4	2	2	2	3			1	1	1	2		1	1		3	2		2		2	1	50
16	27	35		7	14	5		2		3	4		2	3	3	5	1	2	1	5	2	2	3	1	4	1			1	3				1		3	2	3	4		3	1	70
17	36	15		5	15	2	1	1	1	1	2	1	1		2	1		3	2	7	1	1	3	1	4		1			1	2		3		1	2			2	1	1	1	50
18	22	14		4	16	2				1	1	1	3	2		1	1		3	1		1	1	3	1	1				2	2		1	2			3	2	3		1	1	40
19	15	0		5	17	2	2		2		2	2	3	4		1			3	2	1	3	2	4	1		2			2	1		2	1	1		1	2	3			1	50
20	33	14		5	18	1	2		2	1	2	2	1	1		1		1	1	1	3	1	2	1	3	1	1	1		2	3		4		1	1	1	1	3	1	1	3	50
21	17	12		6	19	4	2	3	1	1	3		2	2	2	4	1	1		5	1	1	2	2		1		2		3	1	2		2	1	2		2	3	1	2	1	60
22	18	30		2	20	1	1			1					1	1	1		1						1			1		1		2	1	2			1	1	1		1	1	20
23	3	2		3	21	3		2	2		1		1	1					2			2		2	1			1		1			2	2	1	1	1	2	2				30
24	36	19		3	22	2	1	2	2	1					1				1		1		1	2		1	1	1				1	3	2	1		2	1	2			1	30
25	27	14		1	23	1										1			1	2	2				1											1					1		10
26	21	4		5	24	2	3	2	1	1	2		2	4	2	1	1		2	1	1	1		2	2	1		3	1			1		2	1	1	2	3	3	1		1	50
27	27	9		3	25	1	2		1	2	1	1	1		1				1	2	1	1	3	1	1	1		2		2			1						1	1		2	30
28	29	0		2	26	1	1	1				2		1										1	2		2	2		2		1		2	1			1					20
29	5	10		5	27	3			5		3		1	3	1			1			2		1	2	2	1	2	2		1	1		5		4	3		2	2	1		2	50
30	0	5		2	28	1	1			1		1	1	1					1					1	1		1	1		1	1	2	1		1		1		1			1	20
31	3	10		3	29	1		1	2		1	1		2	2			1			1			2	2		1	1		2	1	1	2	1	1	2		1	1				30
32	30	35		4	30	2		2	1	1	1		1	1	2	3		2		3	3			2	3					1	1		2		1	2		1	2	1	2		40
33	8	17		3	31				1	1		1		2		1			2	2	3	1	1	2	1	1		1	1	1	1		1			1		1	1		2	1	30
34	32	18		4	32		2		1		1	1		2	1	2	1	1	2	2	2	1		2	2	1			1	1		1	2	1	1	2	2		3		1	1	40
35	27	33		6	33	1	3		1	1	3	3	1	3	1	1		1	1	2	2	1	3	3	3	2	2	1		2	3	2	4	1	2		2	1	1	1		2	60
36	3	10		1	34	1	1			1	1		1		2				1							1		1															10
37	29	1		3	35	1	3	1		1						2	1	1	1	3	2		2	1	2					1	1	1		1	1	1			2			1	30
38	9	15		3	36	1	1		2		1	1		2		1	1				1		1	1			2	1				2	4	1	2	1		1	2			1	30
39	30	25		134																																							1340
Live

Cell Formulas
Range	Formula
D39,AQ39	D39	=SUM(D2:D38)
AQ2:AQ38	AQ2	=SUM(F2:AP2)

 

[mikerickson]

Yes, just change the A1:A10 to the number of rows that you want.