Consecutive Superbowls

S

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,168
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is the data that I have for the past Superbowls. I am looking for a count to tell me how many consecutive Superbowls (whether they won or lost) a team has been in. The count below is what I am looking for. Thanks in advance!
VBA Consecutive Superbowls.xlsm
AB
1WL
2Green BayKansas City
3Green BayOakland
4New YorkBaltimore
5Kansas CityMinnesota
6BaltimoreDallas
7DallasMiami
8MiamiWashington
9MiamiMinnesota
10PittsburghMinnesota
11PittsburghDallas
12OaklandMinnesota
13DallasDenver
14PittsburghDallas
15PittsburghLos Angeles
16OaklandPhiladelphia
17San FranciscoCincinnati
18WashingtonMiami
19Los AngelesWashington
20San FranciscoMiami
21ChicagoNew England
22New YorkDenver
23WashingtonDenver
24San FranciscoCincinnati
25San FranciscoDenver
26New YorkBuffalo
27WashingtonBuffalo
28DallasBuffalo
29DallasBuffalo
30San FranciscoSan Diego
31DallasPittsburgh
32Green BayNew England
33DenverGreen Bay
34DenverAtlanta
35St. LouisTennessee
36BaltimoreNew York
37New EnglandSt. Louis
38Tampa BayOakland
39New EnglandCarolina
40New EnglandPhiladelphia
41PittsburghSeattle
42IndianapolisChicago
43New YorkNew England
44PittsburghArizona
45New OrleansIndianapolis
46Green BayPittsburgh
47New YorkNew England
48BaltimoreSan Francisco
49SeattleDenver
50New EnglandSeattle
51DenverCarolina
52New EnglandAtlanta
53PhiladelphiaNew England
54New EnglandLos Angeles
55Kansas CitySan Francisco
56Tampa BayKansas City
57Los AngelesCincinnati
58Kansas CityPhiladelphia
input


VBA Consecutive Superbowls.xlsm
FGHI
1Green Bay22
2Kansas City2
3Minnesota2
4Dallas222
5Miami3
6Washington2
7Pittsburgh22
8Denver22
9San Francisco2
10New England23
11Buffalo4
12Seattle2
input
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Here is something that will work, but not very elegant. For 365 only, not 2019.

MrExcelPlayground18.xlsx
ABCDEFGH
1WL
2Green BayKansas CityBuffalo4
3Green BayOaklandDallas222
4New York JetsBaltimoreDenver22
5Kansas CityMinnesotaGreen Bay22
6BaltimoreDallasKansas City2
7DallasMiamiMiami3
8MiamiWashingtonMinnesota2
9MiamiMinnesotaNew England23
10PittsburghMinnesotaPittsburgh22
11PittsburghDallasSan Francisco2
12OaklandMinnesotaSeattle2
13DallasDenverWashington2
14PittsburghDallas
15PittsburghLos Angeles
16OaklandPhiladelphia
17San FranciscoCincinnati
18WashingtonMiami
19Los AngelesWashington
20San FranciscoMiami
21ChicagoNew England
22New YorkDenver
23WashingtonDenver
24San FranciscoCincinnati
25San FranciscoDenver
26New YorkBuffalo
27WashingtonBuffalo
28DallasBuffalo
29DallasBuffalo
30San FranciscoSan Diego
31DallasPittsburgh
32Green BayNew England
33DenverGreen Bay
34DenverAtlanta
35St. LouisTennessee
36BaltimoreNew York
37New EnglandSt. Louis
38Tampa BayOakland
39New EnglandCarolina
40New EnglandPhiladelphia
41PittsburghSeattle
42IndianapolisChicago
43New YorkNew England
44PittsburghArizona
45New OrleansIndianapolis
46Green BayPittsburgh
47New YorkNew England
48BaltimoreSan Francisco
49SeattleDenver
50New EnglandSeattle
51DenverCarolina
52New EnglandAtlanta
53PhiladelphiaNew England
54New EnglandLos Angeles
55Kansas CitySan Francisco
56Tampa BayKansas City
57Los AngelesCincinnati
58Kansas CityPhiladelphia
Sheet2
Cell Formulas
RangeFormula
E2:E13E2=LET(W,A2:A58,L,B2:B58,a,SORT(UNIQUE(VSTACK(W,L))),b,TRANSPOSE(a),c,IF((W=b)+(L=b),1,0),d,VSTACK(DROP(c,1),SEQUENCE(1,COLUMNS(c),0,0)),e,IF(c+d>1,1,0),f,MMULT(SEQUENCE(1,ROWS(e),1,0),e)>0,g,FILTER(a,TRANSPOSE(f)),g)
F2,F6:F8,F11:F13,F9:G10,F4:G5,F3:H3F2=LET(W,$A$2:$A$58,L,$B$2:$B$58,a,SORT(UNIQUE(VSTACK(W,L))),b,TRANSPOSE(a),c,IF((W=E2)+(L=E2),1,0),d,VSTACK(DROP(c,1),SEQUENCE(1,COLUMNS(c),0,0)),e,IF(c+d>1,1," "),f,TRIM(TEXTJOIN("",TRUE,e)),g,TEXTSPLIT(f," "),LEN(g)+1)
Dynamic array formulas.
 
Upvote 0
Thanks, JamesCanale, that seems to do it! I am open to any solution. I appreciate your help and time on this!
 
Upvote 0
This was interesting so I took a shot at it, even though James came up with something already.

Book1
EFGH
1
2Green Bay22
3Kansas City2
4Dallas222
5Miami3
6Pittsburgh22
7San Francisco2
8Washington2
9Denver22
10New England23
11Seattle2
12Minnesota2
13Buffalo4
Sheet7
Cell Formulas
RangeFormula
E2:E13E2=LET(rng,A2:B58, r,ROWS(rng), s,SEQUENCE(r*2,,0), ss,SEQUENCE(r,,0), u,UNIQUE(INDEX(rng,MOD(s,r)+1,INT(s/r)+1)), tu,TRANSPOSE(u), co,COUNTIF(OFFSET(rng,ss,0,1),tu), ct,COUNTIF(OFFSET(rng,ss+1,0,1),tu), tm,TRANSPOSE(MMULT(SEQUENCE(,r,,0),co*ct)), FILTER(u,tm>0) )
F2:G2,F9:G10,F6:G6,F4:H4,F3,F5,F7:F8,F11:F13F2=LET(r,ROW(A$2:A$58), a,COUNTIF(OFFSET(A$2:B$2,r-ROW(A$2),0),E2), f,FREQUENCY(IF(a,r),IF(a=0,r)), TRANSPOSE(FILTER(f,f>1)) )
Dynamic array formulas.


I'm limited to Excel 2021 functions, so my E2 formula ended up longer, but I came up with a shorter version of the F2 formula.
 
Upvote 0
Just for the fun of it, another 365 option
Fluff.xlsm
ABCDEFGH
1WL
2Green BayKansas CityGreen Bay22
3Green BayOaklandKansas City2
4New York JetsBaltimoreMinnesota2
5Kansas CityMinnesotaDallas222
6BaltimoreDallasMiami3
7DallasMiamiWashington2
8MiamiWashingtonPittsburgh22
9MiamiMinnesotaDenver22
10PittsburghMinnesotaSan Francisco2
11PittsburghDallasNew England23
12OaklandMinnesotaBuffalo4
13DallasDenverSeattle2
14PittsburghDallas
15PittsburghLos Angeles
16OaklandPhiladelphia
17San FranciscoCincinnati
18WashingtonMiami
19Los AngelesWashington
20San FranciscoMiami
21ChicagoNew England
22New YorkDenver
23WashingtonDenver
24San FranciscoCincinnati
25San FranciscoDenver
26New YorkBuffalo
27WashingtonBuffalo
28DallasBuffalo
29DallasBuffalo
30San FranciscoSan Diego
31DallasPittsburgh
32Green BayNew England
33DenverGreen Bay
34DenverAtlanta
35St. LouisTennessee
36BaltimoreNew York
37New EnglandSt. Louis
38Tampa BayOakland
39New EnglandCarolina
40New EnglandPhiladelphia
41PittsburghSeattle
42IndianapolisChicago
43New YorkNew England
44PittsburghArizona
45New OrleansIndianapolis
46Green BayPittsburgh
47New YorkNew England
48BaltimoreSan Francisco
49SeattleDenver
50New EnglandSeattle
51DenverCarolina
52New EnglandAtlanta
53PhiladelphiaNew England
54New EnglandLos Angeles
55Kansas CitySan Francisco
56Tampa BayKansas City
57Los AngelesCincinnati
58Kansas CityPhiladelphia
Sheet4
Cell Formulas
RangeFormula
E2:H13E2=LET(u,UNIQUE(TOCOL(A2:B58)),c,(TOROW(A2:A58)=u)+(TOROW(B2:B58)=u),s,SEQUENCE(,COLUMNS(c)),b,BYROW(c,LAMBDA(br,LET(f,FREQUENCY(IF(br=1,s),IF(br=0,s)),TEXTJOIN("|",,FILTER(f,f>1,""))))),x,DROP(REDUCE("",b,LAMBDA(y,z,VSTACK(y,IF(z="","",--TEXTSPLIT(z,"|",,1))))),1),IFNA(FILTER(HSTACK(u,x),b<>""),""))
Dynamic array formulas.
 
Upvote 0
Nice Fluff!!!!!! I appreciate you taking this on! I always appreciate your posts!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to Count number of wins between two players
2
Replies
11
Views
260
Peter_SSs
Peter_SSs
B
  • Question
Counting consecutive occurrences
Replies
4
Views
257
Peter_SSs
Peter_SSs
L
  • Solved
Find Correlation between ICP Score and Client Won
Replies
3
Views
79
DRSteele
DRSteele
Patcheen
  • Solved
Getting wrong result
Replies
7
Views
230
Fluff
Fluff
A
  • Question
Formula to count consecutive numbers in each row of a data source
Replies
3
Views
354
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,136
Messages
6,123,246
Members
449,093
Latest member
Vincent Khandagale

Share this page

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
Back
Top