MS Excel: how can I make Max() more efficient?

I have a set of data that looks like this:

ID  Value   MaxByID  
0   32  80  
0   80  80  
0   4   80  
0   68  80  
0   6   80  
1   32  68  
1   54  68  
1   56  68  
1   68  68  
1   44  68  
2   54  92  
2   52  92  
2   92  92  
4   68  68  
4   52  68开发者_如何学Go  
5   74  74  
5   22  74  
6   52  94  
6   52  94  
6   46  94  
6   94  94  
6   56  94  
6   14  94    

I am using {=MAX(IF(A$2:A$100=A2,B$2:B$100))} to calculate the MaxByID column. However, the dataset has >100k rows, with mostly unique IDs: this seems to be a really inefficient way to do this, as each cell in C:C has to iterate through every cell in A:A.

The ID field is numeric and can be sorted- is there a way of more intelligently finding the MaxByID?

---

You may be able to use a pivot table to find the maximum for each unique ID: see this link for an example.

Once you have that table, VLOOKUP should enable you to quickly find MaxByID for each ID.

---

Once you have sorted by ID you could add columns to get the start row number and count for each unique.
These 2 numbers allow you to calculate the size and position of the range of Unique values.
So then you can use MAX(OFFSET(StartValueCell,StartThisUnique-1,0,CountThisUnique,1)) to get the max

---

This might be faster

{=IF(A2=A1,C1,MAX(($A$2:$A$24=A2)*($B$2:$B$24)))}

Since your data appears to be sorted, you could see if the ID matches the row above and simply copy the max down.