Show top five records based on meeting multiple criteria

[Guest]

I currently have a spreadsheet of ithousands of investment data recods that
each have specific criteria:

Examples
Fund- 1,2,3, etc.
Issuer - IBM, Ford, etc
Asset - Common Stock, Bond, etc
Country - USA, China, France,etc
Category - 1,2,3,4, etc
Industry - Auto, Health care, manufacturing
MV - Market Value


On a daily basis I refresh the data with current market price information.

At that point I need to be able to update a report that shows the top five
issuers in each Category (1,2,3,4,etc) by Market Value for each fund. I
currently run a pivot table on the data table and sort descending and then
copy and paste the results into the report. The problem is that I have to do
this approx. 50 times per week and it takes alot of time.

Example
I would like the report to link to the data table and somehow be able to
total the MV of all Category 1's, 2's etc and then list the top 5 in each
category in descending order. I need to do this for each of 3 funds( the
fund (1,2,3) are noted in one of the columns described above.

Category 1
IBM $567,897
Ford $234,152
3
4
5

Category 2
Fiat $545,666
Toyota $332,123
3
4
5

etc

Is this something I can do in excel or do I need something else?

Any suggestions would be appreciated. I can provide a sample of the data if
that helps.

 

[Dnereb]

It is possible in Excell but Access is far better for this (or any other
database)

to find the top 5 of something in a database youy can use a SQL query
like

"SELECT TOP 5 Table.Asset FROM Table"

BTW 1000 or more records of data should be stored in a database anyways

 

[Guest]

Thanks, in the short term I am stuck with Excel.

 

[Don]

If you created as many pivot tables (preset for each catagory) as you needed
for your report and used "Field Settings/Advanced/Descending/Using field:
Top five numbers" you would not have to sort each time.
Then link the pivot table data to your report.

A macro like the one below would let you refreash all the pivot tables in
the workbook at once.

Sub test()
ActiveWorkbook.RefreshAll

End sub

Don

 

[Guest]

Don, Let me work on your idea. I think it may be workable.
Thanks and regards,
Joe