I have a spreadsheet full of NFL game predictions this week. Here is
a quick abbreviated sample of my table:
(Actual table has 207 rows, 17 columns)

Name B C D E F Q
--------------------------------- ...... -----
Tom Mia SF Car Hou Phi ...... Dal
David Mia SF NO Hou Was ...... NYG
Barbara Mia Ram NO Hou Phi ......NYG
....
Sara NYJ SF NO Ten Phi ...... Dal (row 207)

(The important data that I need to analyze is within the range B1:Q207)

Here is a list of the game matchups (or string values) for
each column:

Col B - Mia or NYJ Col J - NE or Buf
Col C - SF or Ram Col K - AZ or Sea
Col D - NO or Car Col L - Oak or SD
Col E - Hou or Ten Col M - KC or Den
Col F - Phi or Was Col N - Atl or TB
Col G - GB or Det Col O - Bal or Cin
Col H - Min or Chi Col P - Cle or Pit
Col I - Jac or Ind Col Q - NYG or Dal

If I were to create a list of game winners in a list variable
(or similar data type), what is the best way to search the data
table range (B1:207) to find which row has the most winning
matches?

For example, suppose the following teams win tomorrow:

"Mia, Ram, Car, Ten, Was, Det, Min, Ind, Buf, AZ, Oak, KC, TB, Cin, Cle,
NYG"

Now I need to search the data table for which row has the most matches to
this data.
What is the best way to do this in VBA?

Robert Crandal submitted this idea :
I have a spreadsheet full of NFL game predictions this week. Here is
a quick abbreviated sample of my table:
(Actual table has 207 rows, 17 columns)

Name B C D E F Q
--------------------------------- ...... -----
Tom Mia SF Car Hou Phi ...... Dal
David Mia SF NO Hou Was ...... NYG
Barbara Mia Ram NO Hou Phi ......NYG
...
Sara NYJ SF NO Ten Phi ...... Dal (row 207)

(The important data that I need to analyze is within the range B1:Q207)

Here is a list of the game matchups (or string values) for
each column:

Col B - Mia or NYJ Col J - NE or Buf
Col C - SF or Ram Col K - AZ or Sea
Col D - NO or Car Col L - Oak or SD
Col E - Hou or Ten Col M - KC or Den
Col F - Phi or Was Col N - Atl or TB
Col G - GB or Det Col O - Bal or Cin
Col H - Min or Chi Col P - Cle or Pit
Col I - Jac or Ind Col Q - NYG or Dal

If I were to create a list of game winners in a list variable
(or similar data type), what is the best way to search the data
table range (B1:207) to find which row has the most winning
matches?

For example, suppose the following teams win tomorrow:

"Mia, Ram, Car, Ten, Was, Det, Min, Ind, Buf, AZ, Oak, KC, TB, Cin, Cle, NYG"

Now I need to search the data table for which row has the most matches to
this data.
What is the best way to do this in VBA?

I'd pass the delimited string of winners to a function via InputBox and
have the procedure loop each cell in each row and increment a counter
for each team found listed in the delimited string using the InStr()
function. Use a master counter to determine which row has the most
winning teams.

Pseudo code...
Function GetMostWinnersRow() As Long
Dim n&, k&, i&, j& 'As Long
Dim sWinners As String
sWinners = InputBox("Enter the winning teams")
For i = 1 To 207
For j = 2 To 17
'Count number of cells with winners
If InStr(sWinners, Cells(i, j).Value) 0 Then k = k + 1
Next 'j
'Compare to current highest count & store if greater
'and get the row number to return
If k n Then n = k: GetMostWinnersRow = i
Next 'i
End Function 'GetMostWinnersRow()

Alternatively, you could store the delimited string of winning teams in
a cell and just put it into sWinners without using InputBox so you have
a record you can keep track of from week to week. Just replace...

sWinners = InputBox("Enter the winning teams")

...with...

sWinners = Cells(Row, Col).Text
...where Row/Col is where the delimited string is stored on the active
sheet. If stored elsewhere, use a fully qualified ref to the
sheet.range.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

It happens that GS forgot to reset the temp counter...

Function GetMostWinnersRow() As Long
Dim n&, k&, i&, j& 'As Long
Dim sWinners As String
sWinners = InputBox("Enter the winning teams")
For i = 1 To 207
For j = 2 To 17
'Count number of cells with winners
If InStr(sWinners, Cells(i, j).Value) 0 Then k = k + 1
Next 'j
'Compare 'k' to current highest count (n) & store 'k' if greater
'and get the row number to return
If k n Then n = k: GetMostWinnersRow = i
k = 0 'reset for next row
Next 'i
End Function 'GetMostWinnersRow()

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sat, 31 Dec 2011 20:12:05 -0700, "Robert Crandal"
wrote:

I have a spreadsheet full of NFL game predictions this week. Here is
a quick abbreviated sample of my table:
(Actual table has 207 rows, 17 columns)

Name B C D E F Q
--------------------------------- ...... -----
Tom Mia SF Car Hou Phi ...... Dal
David Mia SF NO Hou Was ...... NYG
Barbara Mia Ram NO Hou Phi ......NYG
...
Sara NYJ SF NO Ten Phi ...... Dal (row 207)

(The important data that I need to analyze is within the range B1:Q207)

Here is a list of the game matchups (or string values) for
each column:

Col B - Mia or NYJ Col J - NE or Buf
Col C - SF or Ram Col K - AZ or Sea
Col D - NO or Car Col L - Oak or SD
Col E - Hou or Ten Col M - KC or Den
Col F - Phi or Was Col N - Atl or TB
Col G - GB or Det Col O - Bal or Cin
Col H - Min or Chi Col P - Cle or Pit
Col I - Jac or Ind Col Q - NYG or Dal

If I were to create a list of game winners in a list variable
(or similar data type), what is the best way to search the data
table range (B1:207) to find which row has the most winning
matches?

For example, suppose the following teams win tomorrow:

"Mia, Ram, Car, Ten, Was, Det, Min, Ind, Buf, AZ, Oak, KC, TB, Cin, Cle,
NYG"

Now I need to search the data table for which row has the most matches to
this data.
What is the best way to do this in VBA?

Not directly related to your problem, but this Microsoft public
template might have a few elements for your template you can use.
Feel free to do so, BTW.

http://office.microsoft.com/en-us/templates/results.aspx?qu=football&ex=1&av=zxl#ai:TC10193976 7|