Max amount within a range

[Sun Ding]

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.

 

[BarryL]

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

 

[Sun Ding]

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.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1	ID	Rate	ID	Rate
2	A01	22.53	A01	22.53
3	A02	22.64	A02	22.64
4	A03	22.98	A03	22.98
5	A04	22.98	A04	23.63
6	A04	22.98	A05	24.32
7	A04	23.4	A06	24.56
8	A04	23.63	A07	24.68
9	A05	24.32	A08	25.05
10	A06	24.56	A09	25.47
11	A07	24.68	A10	24.68
12	A08	25.05	A11	25.05
13	A09	25.47	A12	25.47
14	A09	24.56	A13	26.01
15	A10	24.68
16	A11	25.05
17	A12	25.47
18	A13	25.91
19	A13	26.01
Sheet2

Cell Formulas
Range	Formula
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.

 

[BarryL]

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))

 

[Sun Ding]

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!!

 

[Fluff]

Glad we could help & thanks for the feedback