Multiple variables, loops, & code optimisation

Hi, I’m working on a spreadsheet which contains the results of 7000+ online 8 ball/9 ball pool matches. I thought that since many of the players have played each other multiple times it would be fun to produce some head-to-head statistics, showing who has won how many matches against whom.

So far I’ve written a macro which searches the 7000 entries for a player name (called PlayerA in the spreadsheet). This prompts for a player name using an Input box and copies all the matches from the MatchData sheet into the Query sheet – which is basically a list of results. When this is done, it will look like something below:

http://i1284.photobucket.com/albums/...ps8by3v4by.jpg

In cell C10, the player I searched for, “rowunbeat”, is named, and the number of matches is displayed below this in C11.

What I need help with is how to compare the scores in columns F and G to find the highest, link them to the player name, and fill out cells C12:C15 and I12:I15. It looks like the code could become complicated, and I’d have to assign variables to the five main columns, called Player1, Score1, Score2, Player2, and Decider, as well as the player names in C10 and I10. Then check whether Score1 was greater than, equal to, or less than Score2; then check whether the textstring Player1 or Player2 matches PlayerA (the name in C10), then add 1 to the appropriate cell below their name depending on whether they won, lost or drew the match. I would also need a variable for looping down the rows, to keep track of which row entry I’m up to. This sounds very messy to me, since it looks like it will require multiple Ifs and Loops within each other, and I’m looking for a clue where to start, or whether there’s an easier way of doing this. This is my first attempt at coding in vba since 1999, and this probably shows in the existing code …

*Additional info*: 1. The ‘deciding frame win’ column (column I) is meant to be counted in either C15 or I15, depending on which player has the highest score. As long as these totals are kept distinct, it will be easy to calculate this as a percentage later. The other additional columns (tournament name, type, WT, and Round) are only extra information.
2. Eventually I hope to add a second search to this page which allows PlayerA to be compared to a specific second player (at the moment they are compared to Everyone Else). I think this should be easy enough for me to work out if I can get the code sorted out above, since it would use variations of the initial search I’ve already done.

Thanks for your response. I should’ve been clearer in the “additional info” in the original post about what I want the spreadsheet to do ultimately. Still, I think the work you’ve done is pretty close to what I tried to describe.

At the moment, when I select a player from the combobox, I get separate totals for the same player in columns E and I. You’ve colour-coordinated this – sometimes the player is in E, sometimes in I, depending on how the data was entered. But ultimately, of course, these are the same player and the totals should be combined:
Attachment 379681

Originally, I was going to say the Column I player name should be “Everyone Else”, meaning that the numbers were basically an inversion of the column E player: e.g. if Appu played 41 and won 17, it means Everyone Else won 24 – i.e. Appu’s ‘Lost’ total – but this is just duplication and unnecessary I think, and it was misleading of me to even mention the I column at this stage. So it’s probably best that if a player is picked from the combobox that I10:I15 are just empty at this point. I hope it’s easy enough to combine the figures from column I into the totals in E11:E15.

The final stage I was going to try and work on was a direct comparison between two players. I don’t know if this could be achieved with another combobox, but I guess if it could it might look like this:
Attachment 379682

My idea here is that selecting Appu from the combobox you added would find all of Appu’s matches from the MatchData sheet – which indeed it currently does. And that selecting another player from a second combox (in this case ‘aurora’) would filter the list again, to get matches just between these two players.

Perhaps the point of the ‘Deciders won’ and ‘Deciding frame win’ is clearer here. There’s only 1 row with a ‘D’ in this column – row 17 – which shows there was a close match which Appu won 10-9. This would count 1 in Appu’s stats and 0 in Aurora’s. (I’ve added ‘D’s to certain matches, depending on the rules and format of particular matches – I’ve consciously left out many 5-4 matches because they used different rules, I’m aware of this.) At the point where just one player’s results have been filtered, the Deciders Won column could just count the number of wins. Or (better) perhaps display the % of deciding frame wins – the total won against the count of appearances of ‘D’ in column I - although I think I could do this myself. Thanks again.