Making An Aggregate College Football Poll And Need Help Constructing A Formula For It

J

jwebb1272

New Member
Joined
Sep 1, 2009
Messages
1
I have a website where users can submit weekly college football polls. I'd like to use Excel to help formulate all of the submitted ballots into one aggregate poll but I'm a bit of a novice with the program. Here's an example of what one ballot looks like.

<TABLE style="WIDTH: 138pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=184 border=0 x:str><COLGROUP><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6729" width=184><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 138pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=184 height=21>Team01: Florida</TD></TR>


<TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team02: Texas</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team03: California</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team04: Oklahoma</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team05: Southern California</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team06: Oklahoma State</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team07: LSU</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team08: Virginia Tech</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team09: Ohio State</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team10: Ole Miss</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team11: Alabama</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team12: Georgia Tech</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team13: Penn State</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team14: Oregon</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team15: Iowa</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team16: Notre Dame</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team17: North Carolina</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team18: Kansas</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team19: Rutgers</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team20: Boise State</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team21: Georgia</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team22: Nebraska</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team23: Illinois</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team24: TCU</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Team25: Florida State</TD></TR>

</TBODY></TABLE>

So far I've copy/pasted every ballot into it's own column, leaving Column A open. I'd like to create a points system where a 1st place vote=25 points, 2nd place=24 points, etc. I also copy/pasted a list of every D1A college football team (whether in a ballot or not) at the bottom of the Excel document so the document can recognize each team. However, that's as far as I've gotten. Not sure that I've explained it well, but if anybody was able to get the gist of this post and could take me on a step by step process, I'd really appreciate it.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
This layout is similar to yours. Just keep adding ballots to the green section and when you're done, expand the first part of the formula to encompass the full range of columns. The rest will keep working on its own...

Excel Workbook
ABCDEFG
1TeamScorePointsBallot1Ballot2Ballot3
2Alabama4025FloridaIllinoisOklahoma State
3Boise State4624TexasPenn StateFlorida State
4California2623CaliforniaNebraskaSouthern California
5Florida5022OklahomaFloridaLSU
6Florida State2621Southern CaliforniaOregonBoise State
7Georgia2920Oklahoma StateOklahomaGeorgia
8Georgia Tech4719LSUBoise StateVirginia Tech
9Illinois3518Virginia TechGeorgia TechAlabama
10Iowa3217Ohio StateKansasNotre Dame
11Kansas3716Ole MissOklahoma StateIowa
12LSU4915AlabamaVirginia TechGeorgia Tech
13Nebraska3714Georgia TechTexasOle Miss
14North Carolina2413Penn StateOhio StateOhio State
15Notre Dame3812OregonTCUKansas
16Ohio State4311IowaNotre DameRutgers
17Oklahoma5110Notre DameRutgersNebraska
18Oklahoma State619North CarolinaNorth CarolinaOklahoma
19Ole Miss338KansasLSUOregon
20Oregon417RutgersAlabamaIllinois
21Penn State416Boise StateSouthern CaliforniaNorth Carolina
22Rutgers285GeorgiaIowaTexas
23Southern California504NebraskaGeorgiaPenn State
24TCU163IllinoisOle MissFlorida
25Texas432TCUCaliforniaTCU
26Virginia Tech521Florida StateFlorida StateCalifornia
Sheet4
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

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