Find MAX

[Eva Shanley]

I need to find the largest quantity ordered from a list of
purchase order numbers, and then delete the rows without
the largest quantity. My problem is I could have a
purchase order number repeated 25 times, then the next PO
number kicks in and will be repeated any number of times.
So I need to find the MAX quantity within each range of PO
numbers. Since the data actually comes from a data query
and is sorted by PO number, then by quantity, I can delete
the rows easily enough, but I was curious to know what
formula I could use to get that high number from each
group of POs. Thanks as always.

 

[GerryK]

With your data in [A]
=LARGE($A$2:$A$100,ROW()-ROW($B$2)+1) put in B2 may help
you, and you can drag this down to get a sort list.

 

[NH]

say COL A has PO # and Col B has Quantity then on Col c type the following
formula

=B2=MAX(IF($A$2:$A$100=A2,$B$2:$B$100,FALSE))
and array enter it (press ctrl-shift-enter)
Copy that formula all across column C
The result will show "TRUE" for PO that have the maximum quantity and
"FALSE" for the rest.
Ussing data filter you can hide it or paste special values , then sort and
delte all rows that have false on it

 

[Ron Rosenfeld]

I need to find the largest quantity ordered from a list of
purchase order numbers, and then delete the rows without
the largest quantity. My problem is I could have a
purchase order number repeated 25 times, then the next PO
number kicks in and will be repeated any number of times.
So I need to find the MAX quantity within each range of PO
numbers. Since the data actually comes from a data query
and is sorted by PO number, then by quantity, I can delete
the rows easily enough, but I was curious to know what
formula I could use to get that high number from each
group of POs. Thanks as always.

Data/Subtotal

At each change in PO (purchase order)
Use Function: MAX
Add Subtotal to: Quantity

<OK>

Then you will note in the extreme left, three little numbers (1, 2 and 3).
Click on the '2' to collapse down to the MAX Values.
=================

Data/Pivot Table
<Next>
<Finish>

Drag PO (Purchase Order) to the Row area
Drag Quantity to the Data area

Right Click on the Pivot Table in the Data area and choose Field Settings. The
dialog box should show Quantity as the Source. Put in whatever name you choose
in the Name box, and select Max under the Summarize By:


--ron

 

[Jamie Collins]

I need to find the largest quantity ordered from a list of
purchase order numbers, and then delete the rows without
the largest quantity. My problem is I could have a
purchase order number repeated 25 times, then the next PO
number kicks in and will be repeated any number of times.
So I need to find the MAX quantity within each range of PO
numbers. Since the data actually comes from a data query
and is sorted by PO number, then by quantity

Since the data actually comes from a data query, it would be easier to
change the query <g>. If your existing query looked like this:

SELECT PO_nbr, PO_quantity
FROM MyTable
ORDER BY PO_nbr, PO_quantity
;

then the query you require would look like this:

SELECT PO_nbr, MAX(PO_quantity)
FROM MyTable
GROUP BY PO_nbr
ORDER BY PO_nbr
;

Jamie.

--