Smarter way to identify the top n in a list of stocks

[PV398]

Hi Can anyone  suggest a smarter way to do this?

I am using Excel 2019

My Goal: Keep track of Stocks, ETFs and Mutual funds, based on dividends and an amount to invest.

I have a Database excel worksheet that keeps a list of stocks, dividends and notes

The price of the stocks is being updated automatically in real time.

Company

Ticker

Price

Type

Pay

Yearly Dist.Dec 2022

M Dist.

Potential Shares

Monthly  DIV

Projected Annual  DIV

ROI

DIV TYPE

Notes

For each category, (Stock/BDC, ETF and Mutual Funds) I have alphabetically set these by Ticker  

When I enter an amount to Invest the Number of Potential shares and the Projected Annual Dividend is computed.

I then have another sheet that looks at the top 10 (including all ) by using

'=INDEX(Projected_Annual__DIV,  MATCH(LARGE(Projected_Annual__DIV,1),Projected_Annual__DIV,0)), etc

To generate a table of the top 10 investment returns. Here I am referencing the database sheet for ALL entries.

Here is my question:

To find only Stocks and BDCs, I created another work sheet where I copied the data from the Database work sheet to this BDC ETF Stocks sheet.

To get a list of the top 10 in this category I use

‘=INDEX(BDC_Stocks,  MATCH(LARGE(BDC_Annual_DIV,1),BDC_Annual_DIV,0))

(BDC_Stocks refers to stocks listed in this worksheet).

I do the same for the Mutual Funds.

Is there a smarter way to generate the top 10 by Dividend for each of these 3 categories

1. ALL Stocks
2. BDC/ETF and other Stocks
3. Mutual Funds

Without having to copy the data for the last 2 to create new tables for indexing ? This is a problem if I start adding new stocks or funds to my list.

Would appreciate any suggestions, without having to write code

Thanks

[#2]

If you have Excel 365 then I would suggest you look at the SORT and SORTBY functions. Once you get it sorted you can use TAKE() to take the top x rows. You might also benefit from the FILTER function to pull the stocks you are interested in. While you're at it I recommend you learn LET() because that will help in lots of cases. There are a LOT of new functions but the above are probably the ones that you may want in this case.
Beyond that I would need more like sample sheets and such to better understand what exactly you are doing/needing

[#3]

Unfortunately I do not have Excel 365.
All I am trying to do is make sublists of a Bigger list and then identify the top 10 rows of each sublist. I need to be able to add new elements to the Big List and have the sublists be updated accordingly

[#4]

@PV398 

I would suggest a pivot table with Top10 filter.

[#5]

Do I need to create 3 pivot tables ?

[#6]

@PV398 

At least two pivot tables.

One for ALL and one for the other two categories.