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:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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

New Member
Joined
Nov 20, 2006
Messages
35
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
How about
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">ID</td><td style=";">Rate</td><td style=";">ID</td><td style=";">Rate</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A01</td><td style="text-align: right;;">22.53</td><td style=";">A01</td><td style="text-align: right;;">22.53</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A02</td><td style="text-align: right;;">22.64</td><td style=";">A02</td><td style="text-align: right;;">22.64</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A03</td><td style="text-align: right;;">22.98</td><td style=";">A03</td><td style="text-align: right;;">22.98</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A04</td><td style="text-align: right;;">22.98</td><td style=";">A04</td><td style="text-align: right;;">23.63</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A04</td><td style="text-align: right;;">22.98</td><td style=";">A05</td><td style="text-align: right;;">24.32</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">A04</td><td style="text-align: right;;">23.4</td><td style=";">A06</td><td style="text-align: right;;">24.56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">A04</td><td style="text-align: right;;">23.63</td><td style=";">A07</td><td style="text-align: right;;">24.68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">A05</td><td style="text-align: right;;">24.32</td><td style=";">A08</td><td style="text-align: right;;">25.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">A06</td><td style="text-align: right;;">24.56</td><td style=";">A09</td><td style="text-align: right;;">25.47</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">A07</td><td style="text-align: right;;">24.68</td><td style=";">A10</td><td style="text-align: right;;">24.68</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">A08</td><td style="text-align: right;;">25.05</td><td style=";">A11</td><td style="text-align: right;;">25.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">A09</td><td style="text-align: right;;">25.47</td><td style=";">A12</td><td style="text-align: right;;">25.47</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">A09</td><td style="text-align: right;;">24.56</td><td style=";">A13</td><td style="text-align: right;;">26.01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">A10</td><td style="text-align: right;;">24.68</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">A11</td><td style="text-align: right;;">25.05</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">A12</td><td style="text-align: right;;">25.47</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">A13</td><td style="text-align: right;;">25.91</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">A13</td><td style="text-align: right;;">26.01</td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$19,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$C$1:C1,$A$2:$A$19</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$2:$A$19=C2,$B$2:$B$19</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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

New Member
Joined
Nov 20, 2006
Messages
35
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,089,503
Messages
5,408,676
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top