Consecutive Superbowls

[Stephen_IV]

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 A B 1 W L 2 Green Bay Kansas City 3 Green Bay Oakland 4 New York Baltimore 5 Kansas City Minnesota 6 Baltimore Dallas 7 Dallas Miami 8 Miami Washington 9 Miami Minnesota 10 Pittsburgh Minnesota 11 Pittsburgh Dallas 12 Oakland Minnesota 13 Dallas Denver 14 Pittsburgh Dallas 15 Pittsburgh Los Angeles 16 Oakland Philadelphia 17 San Francisco Cincinnati 18 Washington Miami 19 Los Angeles Washington 20 San Francisco Miami 21 Chicago New England 22 New York Denver 23 Washington Denver 24 San Francisco Cincinnati 25 San Francisco Denver 26 New York Buffalo 27 Washington Buffalo 28 Dallas Buffalo 29 Dallas Buffalo 30 San Francisco San Diego 31 Dallas Pittsburgh 32 Green Bay New England 33 Denver Green Bay 34 Denver Atlanta 35 St. Louis Tennessee 36 Baltimore New York 37 New England St. Louis 38 Tampa Bay Oakland 39 New England Carolina 40 New England Philadelphia 41 Pittsburgh Seattle 42 Indianapolis Chicago 43 New York New England 44 Pittsburgh Arizona 45 New Orleans Indianapolis 46 Green Bay Pittsburgh 47 New York New England 48 Baltimore San Francisco 49 Seattle Denver 50 New England Seattle 51 Denver Carolina 52 New England Atlanta 53 Philadelphia New England 54 New England Los Angeles 55 Kansas City San Francisco 56 Tampa Bay Kansas City 57 Los Angeles Cincinnati 58 Kansas City Philadelphia input

VBA Consecutive Superbowls.xlsm F G H I 1 Green Bay 2 2 2 Kansas City 2 3 Minnesota 2 4 Dallas 2 2 2 5 Miami 3 6 Washington 2 7 Pittsburgh 2 2 8 Denver 2 2 9 San Francisco 2 10 New England 2 3 11 Buffalo 4 12 Seattle 2 input

 

[JamesCanale]

Here is something that will work, but not very elegant. For 365 only, not 2019.

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H
1	W	L
2	Green Bay	Kansas City			Buffalo	4
3	Green Bay	Oakland			Dallas	2	2	2
4	New York Jets	Baltimore			Denver	2	2
5	Kansas City	Minnesota			Green Bay	2	2
6	Baltimore	Dallas			Kansas City	2
7	Dallas	Miami			Miami	3
8	Miami	Washington			Minnesota	2
9	Miami	Minnesota			New England	2	3
10	Pittsburgh	Minnesota			Pittsburgh	2	2
11	Pittsburgh	Dallas			San Francisco	2
12	Oakland	Minnesota			Seattle	2
13	Dallas	Denver			Washington	2
14	Pittsburgh	Dallas
15	Pittsburgh	Los Angeles
16	Oakland	Philadelphia
17	San Francisco	Cincinnati
18	Washington	Miami
19	Los Angeles	Washington
20	San Francisco	Miami
21	Chicago	New England
22	New York	Denver
23	Washington	Denver
24	San Francisco	Cincinnati
25	San Francisco	Denver
26	New York	Buffalo
27	Washington	Buffalo
28	Dallas	Buffalo
29	Dallas	Buffalo
30	San Francisco	San Diego
31	Dallas	Pittsburgh
32	Green Bay	New England
33	Denver	Green Bay
34	Denver	Atlanta
35	St. Louis	Tennessee
36	Baltimore	New York
37	New England	St. Louis
38	Tampa Bay	Oakland
39	New England	Carolina
40	New England	Philadelphia
41	Pittsburgh	Seattle
42	Indianapolis	Chicago
43	New York	New England
44	Pittsburgh	Arizona
45	New Orleans	Indianapolis
46	Green Bay	Pittsburgh
47	New York	New England
48	Baltimore	San Francisco
49	Seattle	Denver
50	New England	Seattle
51	Denver	Carolina
52	New England	Atlanta
53	Philadelphia	New England
54	New England	Los Angeles
55	Kansas City	San Francisco
56	Tampa Bay	Kansas City
57	Los Angeles	Cincinnati
58	Kansas City	Philadelphia
Sheet2

Cell Formulas
Range	Formula
E2:E13	E2	=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:H3	F2	=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.

 

[Stephen_IV]

Thanks, JamesCanale, that seems to do it! I am open to any solution. I appreciate your help and time on this!

 

[Eric W]

This was interesting so I took a shot at it, even though James came up with something already.

Book1
	E	F	G	H
1
2	Green Bay	2	2
3	Kansas City	2
4	Dallas	2	2	2
5	Miami	3
6	Pittsburgh	2	2
7	San Francisco	2
8	Washington	2
9	Denver	2	2
10	New England	2	3
11	Seattle	2
12	Minnesota	2
13	Buffalo	4
Sheet7

Cell Formulas
Range	Formula
E2:E13	E2	=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:F13	F2	=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.

 

[Stephen_IV]

Thanks Eric W! Much appreciated!

 

[Fluff]

Just for the fun of it, another 365 option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	W	L
2	Green Bay	Kansas City			Green Bay	2	2
3	Green Bay	Oakland			Kansas City	2
4	New York Jets	Baltimore			Minnesota	2
5	Kansas City	Minnesota			Dallas	2	2	2
6	Baltimore	Dallas			Miami	3
7	Dallas	Miami			Washington	2
8	Miami	Washington			Pittsburgh	2	2
9	Miami	Minnesota			Denver	2	2
10	Pittsburgh	Minnesota			San Francisco	2
11	Pittsburgh	Dallas			New England	2	3
12	Oakland	Minnesota			Buffalo	4
13	Dallas	Denver			Seattle	2
14	Pittsburgh	Dallas
15	Pittsburgh	Los Angeles
16	Oakland	Philadelphia
17	San Francisco	Cincinnati
18	Washington	Miami
19	Los Angeles	Washington
20	San Francisco	Miami
21	Chicago	New England
22	New York	Denver
23	Washington	Denver
24	San Francisco	Cincinnati
25	San Francisco	Denver
26	New York	Buffalo
27	Washington	Buffalo
28	Dallas	Buffalo
29	Dallas	Buffalo
30	San Francisco	San Diego
31	Dallas	Pittsburgh
32	Green Bay	New England
33	Denver	Green Bay
34	Denver	Atlanta
35	St. Louis	Tennessee
36	Baltimore	New York
37	New England	St. Louis
38	Tampa Bay	Oakland
39	New England	Carolina
40	New England	Philadelphia
41	Pittsburgh	Seattle
42	Indianapolis	Chicago
43	New York	New England
44	Pittsburgh	Arizona
45	New Orleans	Indianapolis
46	Green Bay	Pittsburgh
47	New York	New England
48	Baltimore	San Francisco
49	Seattle	Denver
50	New England	Seattle
51	Denver	Carolina
52	New England	Atlanta
53	Philadelphia	New England
54	New England	Los Angeles
55	Kansas City	San Francisco
56	Tampa Bay	Kansas City
57	Los Angeles	Cincinnati
58	Kansas City	Philadelphia
Sheet4

Cell Formulas
Range	Formula
E2:H13	E2	=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.

 

[Stephen_IV]

Nice Fluff!!!!!! I appreciate you taking this on! I always appreciate your posts!

 

[Fluff]

Glad we could help & thanks for the feedback.