Filtering unique names and their lowest scores

[irelands]

Hi,

Can I have some help. I want to create a list of unique names based on their lowest score e.g.

Surname forename Score score
Smith Dave French 3
Smith Dave French 1
Holland Fred Science 2
Holland Fred Science 4
Pile Sally IT 1
Pile Sally IT 3

Becomes.....

Surname forename Score score
Smith Dave French 1
Holland Fred Science 2
Pile Sally IT 1

Any help would be amazing! Thank you advance.

 

[MickG]

This example may do what you want !!!
https://app.box.com/s/xuhoq7x4hub6oj0v96xj5si0e3ut4b3v

 

[irelands]

This example may do what you want !!!
https://app.box.com/s/xuhoq7x4hub6oj0v96xj5si0e3ut4b3v

That is very much what I'm looking for, many thanks. Is there a way of showing the results on a empty sheet (say called "highest score) rather than cutting the lowest score from the same sheet

Thanks in advanced
Steve

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG24Jun42
[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] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Dic.Add Dn.Value, Dn.Offset(, 1).Value
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1).Value < Dic(Dn.Value) [COLOR="Navy"]Then[/COLOR] _
            Dic(Dn.Value) = Dn.Offset(, 1).Value
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Highest Score").Range("A1").Resize(Dic.Count, 2)
     .Value = Application.Transpose(Array(Dic.Keys, Dic.items))
     .Columns.AutoFit
     .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
     
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick