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>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. 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
56,671
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
56,671
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top