1. ## Return Top 5 Products by Branch using Index and Large

Hi,

I would like to get the Top 5 Selling Funds with Amounts of each Branch, and put the values in the Standard Template (Fixed).

I've tried to use the following formula, but it has problems when I come across the second branch e.g. HK East, Excel will return the values of the 1st branch (i.e. Central).

=IFERROR(INDEX(Pivot!\$A\$4:\$C\$12,LARGE(IF(Pivot!\$A\$4:\$A\$12='Standard template'!A3,ROWS(\$A\$1:A1)),ROW(A1)),2),"")

Appreciate if you could help on this.

Regards,
Hiu Wah

2. ## Re: Return Top 5 Products by Branch using Index and Large

Hi. It's MUCH easier if you take the values from the raw data using SUMPRODUCT.

=SUMPRODUCT((Table1[Branch]='Standard template'!A3)*(Table1[Fund Name]='Standard template'!B3)*Table1[Dealing Amount])

Copy down as far as needed.

3. ## Re: Return Top 5 Products by Branch using Index and Large

Hi Glenn,

How about how to get the Top 5 Selling Fund Names of each branch and show in the "Standard Template" worksheet?

Thanks!

4. ## Re: Return Top 5 Products by Branch using Index and Large

I had hoped to do away with the Pivot Table.. However, I got a bit stuck. So.. how does this look

5. ## Re: Return Top 5 Products by Branch using Index and Large

Hi,

That's the problem. You can see the top 5 fund names of HK East branch are those of Central branch which is incorrect.

6. ## Re: Return Top 5 Products by Branch using Index and Large

Maybe this will help. Each branch is selected separately and the following formula is entered after selecting the Dealing Amount range for the branch. This will return the top 5 for the branch.
Formula:
`Please Login or Register  to view this content.`

This formula will match the Branch and the Dealing Amount for the funds in column B:
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Return Top 5 Products by Branch using Index and Large

I think that I have improved the solution that I previously submitted. This assumes that you will limit the number of rows on the Standard Template worksheet to 5 rows per branch. This will return the top 5 values from column C of Raw Data for each branch and also return the proper value from column B on Raw Data.

On the Standard Template worksheet C3 array enter this formula and fill down:
Formula:
`Please Login or Register  to view this content.`

In B3 array enter this formula and fill down:
Formula:
`Please Login or Register  to view this content.`

9. ## Re: Return Top 5 Products by Branch using Index and Large

Hi newdoverman,

Thanks for your improved version. That's really great.

However, would you mind helping me to further amend the formula so that the fields will show in blank (e.g. in last row) if let's say the branch has only 4 selling funds?

10. ## Re: Return Top 5 Products by Branch using Index and Large

If you can use the IFERROR in C3 and fill down: (enter with Ctrl + Shift + Enter)
Formula:
`Please Login or Register  to view this content.`

In B3 enter this with Ctrl + Shift + Enter:
Formula:
`Please Login or Register  to view this content.`

Alternatively, you can continue with the current formulae without change and use Conditional formatting to hide error values.

Select B3 to maybe C100 then in Conditional Formatting, New Rule, Use Formula use this short formula and format the font with white text:
Formula:
`Please Login or Register  to view this content.`

11. ## Re: Return Top 5 Products by Branch using Index and Large

Hi newdoverman,

Thank you very much for your help. That's brilliant!

One more question though I know there might not be a perfect solution. In case the 4th & 5th funds have the same amount, Excel will return the same fund name (i.e. the 4th fund name) in both rows which is not quite right. Just wonder if it can be further fine-tuned.

Cheers!

12. ## Re: Return Top 5 Products by Branch using Index and Large

Is NDM's working? there are 2 entries for AB China fund in central and two in HK east...

13. ## Re: Return Top 5 Products by Branch using Index and Large

Yes, the NDM is working fine. If I use pivot table to get the sum of amount of each fund per branch and use the pivot result as "Table1" then that' fine.

But assume "AB China Fund" & "AB European Fund" have the same amount under Central branch, then it will only return "AB China Fund" in 4th & 5th rows.

14. ## Re: Return Top 5 Products by Branch using Index and Large

I understand your Q... but is this really correct?? See yellow cells.

15. ## Re: Return Top 5 Products by Branch using Index and Large

I've amended the raw data a little bit. In fact, it won't happen to have 2 same fund names per branch. Try the attached.

16. ## Re: Return Top 5 Products by Branch using Index and Large

I have inserted 2 helper columns to "rank" the Dealing Amounts. The helper column on the Raw Data worksheet is in the table. If you make edits to the Dealing Amounts there may be a green error triangle appear in the Rank column. Fill the formula down from D2 to refresh the formulae. There is a helper column on the Standard Template worksheet (column D which has white text to make it invisible) that uses the values from the Raw Data worksheet Rank column. This is a bit of an inconvenience but the formulae like this will not update properly on their own.

17. ## Re: Return Top 5 Products by Branch using Index and Large

Excellent! Thank you so much for your help!!

18. ## Re: Return Top 5 Products by Branch using Index and Large

Here is your file with the amended data. I don't think the name of the fund really affected anything because the name returned is a result of the Dealing Amount and the Branch.

19. ## Re: Return Top 5 Products by Branch using Index and Large

Thank you for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1