Max amount within a range

Sun Ding

New Member
Joined
Nov 20, 2006
Messages
35
Good morning.
I need help with the following, thank you.
There are 2 columns: ID and rate.
Some IDs are repeated, sometimes 3 times, 5 times, 9 times etc.
I need to pick the highest rate for each ID and there are over 20,000 IDs. I tried DMAX but didn't seem to work.
Thank you for your help.


FROM THIS TO THIS
ID Rate ID Rate
A01 22.53 A01 22.53
A02 22.64 A02 22.64
A03 22.98 A03 22.98
A04 22.98 A04 23.63
A04 22.98 A05 24.32
A04 23.40 A06 24.56
A04 23.63 A07 24.68
A05 24.32 A08 25.05
A06 24.56 A09 25.47
A07 24.68 A10 24.68
A08 25.05 A11 25.05
A09 25.47 A12 25.47
A09 24.56 A13 26.01
A10 24.68
A11 25.05
A12 25.47
A13 25.91
A13 26.01



Sorry, didn't realize that there were no spacing after I posted it.
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
i dont know what type of Microsoft Office you have but if its like mine (2010) you can use the below (newer versions have a MAXIFS i think?)

entered with ctrl+shift+enter
=MAX(IF($A$1:$A$18=C1,$B$1:$B$18))

where column A is ID, B is rate and C are the ID's minus duplicates
 
Upvote 0
thank you for your prompt response.
I only got zeros.
I have ID:A1 in cell A2 and Rate:22.53 in cell B2 etc.
and I put the formula in cell C2.

I am using Office 2016.
 
Last edited:
Upvote 0
How about

Excel 2013/2016
ABCD
1IDRateIDRate
2A0122.53A0122.53
3A0222.64A0222.64
4A0322.98A0322.98
5A0422.98A0423.63
6A0422.98A0524.32
7A0423.4A0624.56
8A0423.63A0724.68
9A0524.32A0825.05
10A0624.56A0925.47
11A0724.68A1024.68
12A0825.05A1125.05
13A0925.47A1225.47
14A0924.56A1326.01
15A1024.68
16A1125.05
17A1225.47
18A1325.91
19A1326.01
Sheet2
Cell Formulas
RangeFormula
C2{=IFERROR(INDEX($A$2:$A$19,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$19),0)),"")}
D2{=MAX(IF($A$2:$A$19=C2,$B$2:$B$19))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
put this in C2 and drag down (with cntrl+shift+enter)

=IFERROR(INDEX($A$2:$A$19, MATCH(0,COUNTIF(C$1:C1, $A$2:$A$19),0)),"")

then in D2 put (with cntrl+shift+enter)

=MAX(IF($A$1:$A$18=C1,$B$1:$B$18))
 
Upvote 0
Amazing! Works like gem!
Thank you so much for your help! I spent an hour figuring how to do that, you guys are so brilliant!!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top