Returning Consecutive Super Bowl Wins

S

Stephen_IV

Well-known Member
Joined
Mar 17, 2003
Messages
1,180
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have Superbowl data, what I would like to do is pull out all teams who had consecutive wins and the superbowl that it took place. Results Column D and E.

Thanks in advance!

Book11
ABCDE
1SuperBowlWinner
2Super Bowl I Green Bay PackersGreen Bay PackersI, II
3Super Bowl II Green Bay PackersMiami DolphinsVII, VIII
4Super Bowl III New York JetsPittsburgh SteelersIX, X, XIII, XIV
5Super Bowl IV Kansas City ChiefsSan Francisco 49ersXXIII, XXIV
6Super Bowl V Baltimore ColtsDallas CowboysXXVII, XXVIII
7Super Bowl VI Dallas CowboysDenver BroncosXXXII, XXXIII
8Super Bowl VIIMiami DolphinsNew England PatriotsXXXVIII, XXXIX
9Super Bowl VIII Miami Dolphins
10Super Bowl IX Pittsburgh Steelers
11Super Bowl X Pittsburgh Steelers
12Super Bowl XI Oakland Raiders
13Super Bowl XII Dallas Cowboys
14Super Bowl XIII Pittsburgh Steelers
15Super Bowl XIV Pittsburgh Steelers
16Super Bowl XV Oakland Raiders
17Super Bowl XVI San Francisco 49ers
18Super Bowl XVII Washington Redskins
19Super Bowl XVIII Los Angeles Raiders
20Super Bowl XIX San Francisco 49ers
21Super Bowl XX Chicago Bears
22Super Bowl XXI New York Giants
23Super Bowl XXII Washington Redskins
24Super Bowl XXIII San Francisco 49ers
25Super Bowl XXIV San Francisco 49ers
26Super Bowl XXV New York Giants
27Super Bowl XXVI Washington Redskins
28Super Bowl XXVII Dallas Cowboys
29Super Bowl XXVIII Dallas Cowboys
30Super Bowl XXIX San Francisco 49ers
31Super Bowl XXX Dallas Cowboys
32Super Bowl XXXI Green Bay Packers
33Super Bowl XXXII Denver Broncos
34Super Bowl XXXIII Denver Broncos
35Super Bowl XXXIV St. Louis Rams
36Super Bowl XXXV Baltimore Ravens
37Super Bowl XXXVI New England Patriots
38Super Bowl XXXVII Tampa Bay Buccaneers
39Super Bowl XXXVIII New England Patriots
40Super Bowl XXXIX New England Patriots
41Super Bowl XL Pittsburgh Steelers
42Super Bowl XLI Indianapolis Colts
43Super Bowl XLII New York Giants
44Super Bowl XLIII Pittsburgh Steelers
45Super Bowl XLIV New Orleans Saints
46Super Bowl XLV Green Bay Packers
47Super Bowl XLVI New York Giants
48Super Bowl XLVII Baltimore Ravens
49Super Bowl XLVIII Seattle Seahawks
50Super Bowl XLIX New England Patriots
51Super Bowl L Denver Broncos
52Super Bowl LI New England Patriots
53Super Bowl LII Philadelphia Eagles
54Super Bowl LIII New England Patriots
55Super Bowl LIV Kansas City Chiefs
56Super Bowl LV Tampa Bay Buccaneers
Sheet1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Try this in D2:

=UNIQUE(FILTER(B2:B56;B2:B56=B3:B57))

in E2:

=SUBSTITUTE( TEXTJOIN(",";1;UNIQUE(FILTER($A$2:$A$56;($B$2:$B$56=$B$3:$B$57) + ($B$2:$B$56=$B$1:$B$55);$B$2:$B$56=$D2)));"Super Bowl ";"")
 
Upvote 0
Phouc this is what I am getting.

Book11
DE
2Green Bay PackersI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
3Miami DolphinsI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
4Pittsburgh SteelersI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
5San Francisco 49ersI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
6Dallas CowboysI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
7Denver BroncosI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
8New England PatriotsI,II,VII,VIII,IX,X,XIII,XIV,XXIII,XXIV,XXVII,XXVIII,XXXII,XXXIII,XXXVIII,XXXIX
Sheet3
Cell Formulas
RangeFormula
D2:D8D2=UNIQUE(FILTER(B2:B56,B2:B56=B3:B57))
E2:E8E2=SUBSTITUTE( TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$56,($B$2:$B$56=$B$3:$B$57) + ($B$2:$B$56=$B$1:$B$55),$B$2:$B$56=$D2))),"Super Bowl ","")
Dynamic array formulas.
 
Upvote 0
See this link

docs.google.com

Example Rand Sample.xlsx

Sheet1 SuperBowl,Winner Super Bowl I,Green Bay Packers,Green Bay Packers,I,II,III Super Bowl II,Green Bay Packers,Kansas City Chiefs,IV,V Super Bowl III,Green Bay Packers,Miami Dolphins,VII,VIII Super Bowl IV,Kansas City Chiefs,Pittsburgh Steelers,IX,X,XIII,XIV Super Bowl V,Kansas City Chiefs,Sa...
docs.google.com docs.google.com
 
Upvote 0
See if this adaptation works for you.

Stephen_IV.xlsm
ABCDE
1SuperBowlWinner
2Super Bowl I Green Bay PackersGreen Bay PackersI ,II
3Super Bowl II Green Bay PackersMiami DolphinsVII,VIII
4Super Bowl III New York JetsPittsburgh SteelersIX ,X ,XIII ,XIV
5Super Bowl IV Kansas City ChiefsSan Francisco 49ersXXIII ,XXIV
6Super Bowl V Baltimore ColtsDallas CowboysXXVII ,XXVIII
7Super Bowl VI Dallas CowboysDenver BroncosXXXII ,XXXIII
8Super Bowl VIIMiami DolphinsNew England PatriotsXXXVIII ,XXXIX
9Super Bowl VIII Miami Dolphins
10Super Bowl IX Pittsburgh Steelers
11Super Bowl X Pittsburgh Steelers
12Super Bowl XI Oakland Raiders
13Super Bowl XII Dallas Cowboys
14Super Bowl XIII Pittsburgh Steelers
15Super Bowl XIV Pittsburgh Steelers
16Super Bowl XV Oakland Raiders
17Super Bowl XVI San Francisco 49ers
18Super Bowl XVII Washington Redskins
19Super Bowl XVIII Los Angeles Raiders
20Super Bowl XIX San Francisco 49ers
21Super Bowl XX Chicago Bears
22Super Bowl XXI New York Giants
23Super Bowl XXII Washington Redskins
24Super Bowl XXIII San Francisco 49ers
25Super Bowl XXIV San Francisco 49ers
26Super Bowl XXV New York Giants
27Super Bowl XXVI Washington Redskins
28Super Bowl XXVII Dallas Cowboys
29Super Bowl XXVIII Dallas Cowboys
30Super Bowl XXIX San Francisco 49ers
31Super Bowl XXX Dallas Cowboys
32Super Bowl XXXI Green Bay Packers
33Super Bowl XXXII Denver Broncos
34Super Bowl XXXIII Denver Broncos
35Super Bowl XXXIV St. Louis Rams
36Super Bowl XXXV Baltimore Ravens
37Super Bowl XXXVI New England Patriots
38Super Bowl XXXVII Tampa Bay Buccaneers
39Super Bowl XXXVIII New England Patriots
40Super Bowl XXXIX New England Patriots
41Super Bowl XL Pittsburgh Steelers
42Super Bowl XLI Indianapolis Colts
43Super Bowl XLII New York Giants
44Super Bowl XLIII Pittsburgh Steelers
45Super Bowl XLIV New Orleans Saints
46Super Bowl XLV Green Bay Packers
47Super Bowl XLVI New York Giants
48Super Bowl XLVII Baltimore Ravens
49Super Bowl XLVIII Seattle Seahawks
50Super Bowl XLIX New England Patriots
51Super Bowl L Denver Broncos
52Super Bowl LI New England Patriots
53Super Bowl LII Philadelphia Eagles
54Super Bowl LIII New England Patriots
55Super Bowl LIV Kansas City Chiefs
56Super Bowl LV Tampa Bay Buccaneers
Sheet1
Cell Formulas
RangeFormula
D2:D8D2=UNIQUE(FILTER(B2:B56,B2:B56=B3:B57))
E2:E8E2=SUBSTITUTE(TEXTJOIN(",",1,FILTER($A$2:$A$56,($B$2:$B$56=D2)*(($B$1:$B$55=D2)+($B$3:$B$57=D2)))),"Super Bowl ","")
Dynamic array formulas.
 
Upvote 0
Yes Peter, that did it. Thank you both for the help!!! Much appreciated!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Consecutive Superbowls
Replies
7
Views
438
Fluff
Fluff
T
  • Solved
I want short code some how
Replies
4
Views
576
mohadin
M
JenniferMurphy
  • Question Question
Are these macros the best way to make custom sorts easily accessible?
Replies
2
Views
116
JenniferMurphy
JenniferMurphy
D
  • Solved
Display streak of wins or losses
2
Replies
16
Views
984
dotcanada
D
D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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