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:
[TABLE="width: 596"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Shash[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Yog[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Yog[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Avi[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Ran[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Shash[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Raj[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Raj[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]


Output:
Result must be



[TABLE="width: 557"]
<tbody>[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Top 3 total scores[/TD]
[/TR]
[TR]
[TD]Rank 1[/TD]
[TD]Shash[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Rank 2[/TD]
[TD]Ran[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Rank 3[/TD]
[TD]Yog[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

 

[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??