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

#### Tao1123

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Zot

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
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.

Zot

#### Tao1123

##### New Member
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
1Set 1Set 2Number of Occurences0123456789101112131415161718192021222324252627282930313233343536
21745012123111341221131111131133121150
32125111131111552231411221212113150
4162422111112221121111120
51332331212212211143411130
61613411111132122211121121230
7283145232113321111122111112221240
8013361211113111311131321130
9131437112213211121211313130
107235811143111313432113111111522150
113315392211131111211111121121130
121813510161121411543231321113350
13311911111111211110
142530212111212212111111120
15410513221312126422231112113222150
16273571452342335121522314113132343170
173615515211112112132711314112312211150
18221441621113211311131122123231140
19150517222223413213241221211123150
203314518122122111111312131112341111311350
21171261942311322241151122123122122312160
22183022011111111112121111120
2332321322111222111221112230
24361932221221111121111321212130
2527141231112211110
262145242321122421121112213112112331150
27279325121211111211311122111230
282902261112112222121120
295105273531311212212211543221250
3005228111111111111121111120
31310329112112211221121121121130
3230354302211111232332311212121240
338173311112122311211111111112130
34321843221112121122212211112112231140
3527336331311331311112213332212324122111260
3631013411111211110
3729133513112111322121111112130
3891533611211211111212412112130
3930251341340
Live
Cell Formulas
RangeFormula
D39,AQ39D39=SUM(D2:D38)
AQ2:AQ38AQ2=SUM(F2:AP2)

#### mikerickson

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

Replies
2
Views
34
Replies
13
Views
392
Replies
2
Views
34
Replies
1
Views
158
Replies
1
Views
71

1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back