Creating Top 5 List With Multiple Criteria

[joeyjoejoejnr]

Hello Awesome Excel People!

I'm in the process of creating spreadsheets for a junior football league.
I'm looking to create top 5 lists for each team after each round of the comp.
PS - there are likely to be duplicate scores.

So here is a sample of my data:

First name	Surname	Division	Team	Game #1	Game #2	Games -->
Jim	Jones	1	Hawks	15	2
Dave	Dobbs	4	Hawks	0	21
Reg	Grundy	2	Falcons	11	2
Rex	Brown	1	Ducks	33	4
Brandon	Vera	3	Wildcats	5	3
Tyler	Chai	2	Falcons	4	0
Greg	Roberts	2	Ducks	6	1
Quin	Smith	1	Hawks	21	4
Fred	Bloggs	3	Ducks	12	11
James	Johnson	2	Ducks	12	7
Vincent	Quill	3	Falcons	23	6

<tbody>
</tbody>

So there is going to be about 400 participants across the teams in 4 divisions.
I have defined names of the data for simplicity:
first names (columnA) = FirstName
surnames (columnB) = Surname
division (columnC) = Division
Team (ColumnD) = Team
game #1 (columnE) = game1
game #2 (columnF) = game2
etc.

On other sheets I have a page for each club/team.
I am looking to list the top 5 scorers of each round regardless of division.
I'm looking to add only data in the above sample sheet and have results input automatically.

So for example, the ducks I want to return the following data in a sheet:

Ducks - Game #1 Results

	1st	2nd	3rd	4th	5th
First name	Rex	Fred	James	Greg
Surname	Brown	Bloggs	Johnson	Roberts
Points	33	12	12	6
Division	1	3	2	2

<tbody>
</tbody>

I am open to changing the format of the output table if it will make it possible to do this.

Am I dreaming? or can this be done?

Thank you all!
Joe

 

[MickG]

Try this for results starting in sheet2.
NB:- See Code remarks for more Games

[COLOR="Navy"]Sub[/COLOR] MG12Jun08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

c = 1
For Ac = 5 To 6 '[COLOR="Green"][B] Nb:- Increase this count to the columns Number you have Games in.[/B][/COLOR]
'[COLOR="Green"][B]At the moment it refers to columns "5 & 6"[/B][/COLOR]
col = col + 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
ReDim Ray(1 To 6, 1 To 6)
Ray(1, 1) = K & " :- Game# " & col & "  Results"
Ray(2, 2) = "First  ": Ray(2, 3) = "Second  ": Ray(2, 4) = "Third   ": Ray(2, 5) = "Forth   ": Ray(2, 6) = "Fifth   "
Ray(3, 1) = "First Name"
Ray(4, 1) = "Surname"
Ray(5, 1) = "Points"
Ray(6, 1) = "Division"
nn = 1
[COLOR="Navy"]For[/COLOR] n = 1 To .Item(K).Count
Lg = Application.Large(.Item(K).Offset(, Ac - 4), n)
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] .Item(K).Offset(, Ac - 4)
        [COLOR="Navy"]If[/COLOR] Dn = Lg [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] R = Dn
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(R.Offset(, -2 - col).Value) [COLOR="Navy"]Then[/COLOR]
                    Dic.Add R.Offset(, -2 - col).Value, ""
                    nn = nn + 1
                    Ray(3, nn) = R.Offset(, -3 - col)
                    Ray(4, nn) = R.Offset(, -2 - col)
                    Ray(5, nn) = R.Value
                    Ray(6, nn) = R.Offset(, -1 - col)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet20").Cells(c, "A").Resize(6, 6)
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
    c = c + 7
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Just an Update:- You will need to change this line at bottom of code from "Sheet20" to "sheet2" !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

With Sheets("Sheet20").Cells(c, "A").Resize(6, 6)
to This:-
With Sheets("Sheet2").Cells(c, "A").Resize(6, 6)

 

[joeyjoejoejnr]

Hello Mick G,

Thanks for your speedy response.
I've tried executing the VBA code you have input into the response.

I'm having issues getting it to run. I keep getting errors and it asks me to debug the code.

 

[MickG]

Have a look at this example:-
Your actual data should be basically the same as sheet1, with perhaps extra columns
Replace data in example with your actual data.
If you still have problems, Please Share Your data that does not work .
https://app.box.com/s/2yz9z5cbpjl2k2sfydz7hruvebar25en

 

[joeyjoejoejnr]

Is there a way this can be done with excel formulas rather than VB code?
I attempted to replicate your data sample but still had coding errors.

 

[joeyjoejoejnr]

Disregard, pivot tables can provide me with a method to do what I need. Thank you for your help