MAX value in one coulmn based upon another column [Solved/Closed]

Piggy70 3 Posts Wednesday May 29, 2013Registration date May 30, 2013 Last seen - May 29, 2013 at 05:22 PM - Latest reply: Piggy70 3 Posts Wednesday May 29, 2013Registration date May 30, 2013 Last seen
- May 30, 2013 at 08:55 AM
Batch Price Column C
00015 2.75
00015 2.9995
00016 1.89
00017 0.56116
00018 0.1421
00018 0.11808
00018 0.1605
00018 0.12575
00018 0.1091
00018 0.1745
00018 0.1342
00018 0.1655
00019 0.1421
00019 0.11808

Hi, I have about 55,000 rows like the above and I am trying to get the maximum price within each batch to display in Column C....example, for Batch 00015, the max price should be $2.9995 (displayed in Column C) - I do not know how to do this in EXCEL - can anyone assist? thank you
See more 

2 replies

Kevin@Radstock 42 Posts Thursday January 31, 2013Registration date April 26, 2014 Last seen - May 30, 2013 at 04:30 AM
0
Thank you
Hi

Try the following array (CTRL+ SHIFT + ENTER to commit). Assuming the batch numbers are in A1:A14 & prices are in C1:C14, also assuming that the batch numbers are "text".

=MAX(IF(A1:A14="00015",C1:C14))

If they are numbers in column A, formatted as "00000".

=MAX(IF(A1:A14=15,C1:C14))

You might want to look at the aggregate function if you have Excel 2010>.
Piggy70 3 Posts Wednesday May 29, 2013Registration date May 30, 2013 Last seen - May 30, 2013 at 08:55 AM
0
Thank you
Thank you so much! It works beautifully!