Identifying the highest value for multiple records.

[gseft]

Hello everyone,

I expect this will be something that is very simple, but I am in a pinch and need a resolution to this without me researching. I have a file in column A (ID) I have an account code and each account has multiple ratings Column D. I want to quickly find and select the rows that have the highest value in column D. Some ratings may be duplicated for the account i.e. account 234 may have 4 records with a rating of 8.

Thanks

[TABLE="width: 148"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[TD]00000000514[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000000514[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000000514[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]00000000735[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]00000000735[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000000735[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000000735[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000000735[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]00000000927[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]00000000927[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]00000001563[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001732[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]00000001834[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]00000002074[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]00000002074[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]00000002124[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Are you looking for a formula or VBA?
Also what happens when the account has multiple rows with the max rating?

 

[gseft]

Fluff,

I don't have a preference, I will have multiple files like this that I will need to clean. So whatever is easiest to modify.

If the account has multiple rows at the max rating I want one record.

Thanks

 

[gseft]

Let me also clarify after reading my post this also may be confusing. I want the highest value for each account. So, some accounts will only have a rating of 5 although the highest is 9.

 

[Fluff]

If you want to delete all but one row with the highest rank
Sort the data on ID then on Rating(highest to lowest)
You can then use remove duplicates select the entire data & make sure that only the ID column is checked.

 

[gseft]

So Excel is always going to select the first value? I wasn't sure about that.

 

[Fluff]

Yup, it will keep the first of each duplicate.

 

[gseft]

Yep that is simple. Thanks

 

[MickG]

Another option
Max value for each account retained , other rows deleted !!

[COLOR="Navy"]Sub[/COLOR] MG28Mar17
[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]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", 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(, 3)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 3).Value > .Item(Dn.Value) [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 3)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  [COLOR="Navy"]If[/COLOR] Not Dn.Offset(, 3).Address = .Item(Dn.Value).Address [COLOR="Navy"]Then[/COLOR]
      [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
 [COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fluff]

Yep that is simple. Thanks

You're welcome & thanks for the feedback