Show top five records based on meeting multiple criteria

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.


--
Joe D

--
Joe D

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


"Joe D" wrote:

> 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.
>
>
> --
> Joe D
>
> --
> Joe D

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


Dnereb" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Dnereb
>
>
> ------------------------------------------------------------------------
> Dnereb's Profile:
> http://www.excelforum.com/member.php...o&userid=26182
> View this thread: http://www.excelforum.com/showthread...hreadid=486512
>

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


"Joe D" wrote:

> Thanks, in the short term I am stuck with Excel.
> --
> Joe D
>
>
> "Joe D" wrote:
>
> > 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.
> >
> >
> > --
> > Joe D
> >
> > --
> > Joe D