I have a large spreadsheet (some 25000 lines) with headings including
make, model & price. I use this as a lookup table to value other
lists.
If I have a list of say 100 items I lookup each item using autofilter
and extract from the data table the number of items, the max. price,
min. price and average. I am looking for a way to do this automatically
by using a macro
I know this is a complicated one, can anyone help?


--
tryer
------------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=392501

It sounds like a perfect excuse to learn about pivottables (data|pivotable).

To read more about the pivottable stuff, you may want to look at some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


A very short course.
Select your range (include only the last row of headers)
Data|pivottable
data|pivottable
follow the wizard until you get to the dialog with the Layout button
hit that layout button.

Drag Itemheader to the row area
drag item to the data area--if it doesn't say Count of, double click on that and
choose Count from the dialog.

drag price to the data area--double click to show Max
drag price (again) to the data area--double click to show min
drag price (again) to the data area--double click to show average

You can actually drag make, model to the row field to see subcategories.
Then finish up the wizard.

Now drag the Data button one cell to the right. Drop it directly on the cell
that says Total.

===
After you've got what you want, save that workbook. Then try experimenting by
rightclicking on everything and dragging those button like objects around the
pivottable.

Spend an hour playing with pivottables and you'll wonder how you did anything
without them.

=====
Another option (not so nice).

Sort your data
apply data|subtotals multiple times (don't replace current subtotals)--but
choose the correct function you want--min/max/average.

tryer wrote:

I have a large spreadsheet (some 25000 lines) with headings including
make, model & price. I use this as a lookup table to value other
lists.
If I have a list of say 100 items I lookup each item using autofilter
and extract from the data table the number of items, the max. price,
min. price and average. I am looking for a way to do this automatically
by using a macro
I know this is a complicated one, can anyone help?

--
tryer
------------------------------------------------------------------------
tryer's Profile: http://www.excelforum.com/member.php...o&userid=16546
View this thread: http://www.excelforum.com/showthread...hreadid=392501

--

Dave Peterson