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

Tao1123

New Member
Joined
Mar 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,985
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.
 
  • Like
Reactions: Zot

Tao1123

New Member
Joined
Mar 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top