+ Reply to Thread
Results 1 to 19 of 19

Return Top 5 Products by Branch using Index and Large

  1. #1
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

    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.
    Glenn



  3. #3
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,252

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

    Not sure about this..........
    Attached Files Attached Files

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.


    In B3 array enter this formula and fill down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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?

    Appreciated your help!

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    Please Login or Register  to view this content.


    In B3 enter this with Ctrl + Shift + Enter:
    Formula: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

    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. #13
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,958

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

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

  15. #15
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

    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.
    Attached Files Attached Files

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    04-27-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    8

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

    Excellent! Thank you so much for your help!!

  18. #18
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files

  19. #19
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

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

    Thank you for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to: Return next value w/ same # in index match large function?
    By Yoshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2015, 02:35 AM
  2. Return Row Index and Column Index of a Cell in a Range
    By exceere in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-06-2015, 04:54 AM
  3. Replies: 12
    Last Post: 07-23-2014, 01:29 AM
  4. [SOLVED] Return list of products excluding duplicates
    By Chanley24 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-22-2013, 02:21 AM
  5. Formula to work out how many products among various products!
    By MissConfussed in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-02-2013, 02:24 PM

Bookmarks

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