Find the sum of values against names and list in order of top to bottom. Preferrabally top 5

[JOHN007]

Let us say i have list of students and score against each student. (Name of student may\may not repeat. i the list of top 3 students(Rank) and sum of scores against each student.


Input:

	A	B
1	Shash	40
2	Yog	10
3	Yog	10
4	Avi	10
5	Ran	80
6	Shash	50
7	Raj	5
8	Raj	2

<tbody>
</tbody>

Output:
Result must be

	Name	Top 3 total scores
Rank 1	Shash	90
Rank 2	Ran	80
Rank 3	Yog	20

<tbody>
</tbody>

 

[MickG]

Try this for results starting "D1".

[COLOR="Navy"]Sub[/COLOR] MG16May04
[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] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & 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.Offset(, 1).Value
        [COLOR="Navy"]Else[/COLOR]
            .Item(Dn.Value) = .Item(Dn.Value) + Dn.Offset(, 1).Value
         [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
    Range("D1:F1").Value = Array("Rank", "Name", "Score")
  [COLOR="Navy"]Dim[/COLOR] Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] t
    [COLOR="Navy"]For[/COLOR] n = 1 To 3
        num = Application.Large(.items(), n)
        c = c + 1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
            t = .Item(K)
            [COLOR="Navy"]If[/COLOR] .Item(K) = num [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] Not InStr(nstr, K) > 0 [COLOR="Navy"]Then[/COLOR]
                    nstr = nstr & "," & K
                    Nam = K
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] K
            Cells(c + 1, "D") = "Rank" & c
            Cells(c + 1, "E") = Nam
            Cells(c + 1, "F") = num
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

I've just noticed you want the top 5, Change line below as shown in Red.

For n = 1 To[SIZE=3] [COLOR=#ff0000][B]5[/B][/COLOR][/SIZE]

 

[JOHN007]

I've just noticed you want the top 5, Change line below as shown in Red.

For n = 1 To[SIZE=3] [COLOR=#ff0000][B]5[/B][/COLOR][/SIZE]

can i get in the form of excel formula??