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.