Identifying the highest value for multiple records.

gseft

New Member
Joined
Jul 5, 2018
Messages
7
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

IDRating
000000005145
000000005143
000000005142
000000007359
000000007353
000000007353
000000007353
000000007351
000000009272
000000009272
000000015634
000000017328
000000017328
000000017325
000000017325
000000017325
000000017325
000000017325
000000017325
000000017325
000000018346
000000018346
000000018346
000000018343
000000018343
000000018341
000000018341
000000020749
000000020746
000000021248
000000021248
000000021248
000000021248
000000021248
000000021248
000000021248
000000021248

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,630
Office Version
365
Platform
Windows
Are you looking for a formula or VBA?
Also what happens when the account has multiple rows with the max rating?
 

gseft

New Member
Joined
Jul 5, 2018
Messages
7
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

New Member
Joined
Jul 5, 2018
Messages
7
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,630
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 5, 2018
Messages
7
So Excel is always going to select the first value? I wasn't sure about that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,630
Office Version
365
Platform
Windows
Yup, it will keep the first of each duplicate.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Another option
Max value for each account retained , other rows deleted !!
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,222
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top