Finding The MAX Value

C

carl

My data table looks like this:

ID Date QTY
BOX005 20070103 19626
BOX005 20070104 9618
BOX005 20070105 17465
BOX005 20070108 20612
BOX226 20070109 13310
BOX226 20070110 10859
BOX226 20070111 30335
BOX226 20070112 20015

I am trying to create a table for each ID that shows the Date and Qty of the
Maximum Qty - like this table:

ID Date QTY
BOX005 20070108 20612
BOX226 20070111 30335


The actual data table has around 7000 rows.

Thank you in advance.
 
J

Jim Thomlinson

You can do it as a pivot table. Place your cursor in the middle of your data
and select Data -> Pivot Table and Pivot Chart Report... This will bring up a
wizard. You can probably just select Finish and it will create a pivot table
on a new sheet for you. Drag the ID and Date into the left column and the
Quantity into the Middle. Now right click on the ID and select Field Settings
-> Advanced. Change the Sort order to Desending by Quantity and select Show
Top (1). You can remove the subtotaling and do other formatting to make it
look a bit better...
 
C

carl

Thank You. I could not get this to work for me.

My final table needs to list out for each ID, the Data of the Max QTY and
the associated Max Qty. Like this table:

ID Date QTY
BOX005 20070108 20612
BOX226 20070111 30335


Am I doing something incorrect ?
 
B

bj

use advanced filter or some other method to get the unique list of IDs
assuming your main data is in A:C
set up a location with your headers (D1:F1?)
and your unique list
(D2:D3_
in E2 enter
=index($B$1:$B$100),match(max(($C$1:$C$100)*($A$1:$A$100=D2),$C$1:$C$100),0)
in F2 enter
=index($C$1:$C$100),match(max(($C$1:$C$100)*($A$1:$A$100=D2),$C$1:$C$100),0)
copy E@:F2 and paste down as far as you have unique IDs
 
J

Jim Thomlinson

Did you get a pivot table created?
Did you add the Id's and Dates to the left column?
Did you add the quantities to the middle?
 
J

Jim Thomlinson

Sorry in my original description I suggested using Advanced on the ID. I
meant to say on the date field. My Bad... using your data here is the result
I got...

ID Date Total
BOX005 20070108 20612
BOX226 20070111 30335
 
Top