Finding Min & Max using lookup & variable sized ranges?

[Jay]

Apologies for the rather garbled subject line. It mentions 'lookup' but may
not involve any lookup functions.

I have a 20,000+ row range of data with 3 columns: Product, Bought, Sold.

There are many rows for each product and £values in the 'Bought' and 'Sold'
fields.

However, ProductA may have 8 rows and ProductB may have 13 rows etc. I have
elsewhere in the file a list of Unique values for the Product column. I
want to be able to lookup the smallest 'Bought' value and largest 'Sold'
value for each product. But because each product has a non-fixed number of
rows I'm not sure how to do it. Can anyone advise?

I've achieved the same result by importing into Access and creating a Totals
query which groups by the Product field and returns MIN Bought and MAX Sold.
However, I'm keen to be able to know how to do it in Excel - using formulas
rather than sorting/filtering.

Any help greatly appreciated.

Jason

 

[Peo Sjoblom]

The problem is that with such a large dataset using array formula can slow
down the workbook quite a bit, otherwise it doesn't matter how many of each
product there are

for simplicity I will assume products in A2:A20000, Bought in B2:B20000 and
Sold in C2:C20000 and that your list with unique product names start in G2,
in H2 put

=MIN(IF($A$2:$A$20000=G2,$B$2:$B$20000))


and in I2 put

=MAX(IF($A$2:$A$20000=G2,$C$2:$C$20000))

both entered with ctrl + shift & enter

however the best way to tackle this might be by using a pivot table, drag
the product to the row field then bought and sold into the data field, then
double clicking each field button and select min for bought and max for
sold, that way you would get a table with all unique products and their
respective min and max
You might want to remove the grand totals min and max to make it look better
unless you are interested in that