Re: Finding Min & Max using lookup & variable sized ranges?
- From: "Peo Sjoblom" <terre08@xxxxxxxx>
- Date: Fri, 18 May 2007 07:45:04 -0700
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
--
Regards,
Peo Sjoblom
"Jay" <dummy@xxxxxxxxxxx> wrote in message
news:%23ijl7TVmHHA.4772@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Prev by Date: Finding Min & Max using lookup & variable sized ranges?
- Next by Date: Macro to work on selected cell
- Previous by thread: Finding Min & Max using lookup & variable sized ranges?
- Next by thread: Macro to work on selected cell
- Index(es):
Relevant Pages
|
