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

Show top five records based on meeting multiple criteria
 

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

Show top five records based on meeting multiple criteria
 

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

Show top five records based on meeting multiple criteria
 

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" wrote in
message ...

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

Show top five records based on meeting multiple criteria
 

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