+ Reply to Thread
Results 1 to 5 of 5

Top 5 Table

  1. #1
    Registered User
    Join Date
    07-04-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    15

    Top 5 Table

    Hi all,

    Im looking for a way to return the top 5 selling items into another table.
    I need a formula or way to recognize the highest value in column i and return it along with the product code from column a into the table on the bottom right of my example sheet attached.

    The main table (columns a to col j) changes as the supplier changes, so the data depending on the supplier can be anywhere from 3 line items or rows to 50 with numerous suppliers.

    So therefore i dont want to have to do it manually each time.

    Hope this makes sense.

    Thanks in advance.
    Rob.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: Top 5 Table

    Try this:

    Excel 2016 (Windows) 32 bit
    L
    M
    19
    Top 5 Sellers
    20
    Product Total
    21
    101897052
    3015
    22
    101897058
    2227
    23
    101897054
    1945
    24
    101897056
    1780
    25
    101897018
    1442
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    L
    M
    21
    =INDEX($A$6:$A$24,MATCH(M21,$I$6:$I$24,0))
    =LARGE($I$6:$I$24,ROWS(M$21:M21))
    Sheet: Sheet1
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Top 5 Table

    in K6
    =RANK(I6,I$6:I$24)
    copy down to K24

    in L21
    =INDEX(A$6:A$24,MATCH(ROW()-20,K$6:K$24,0),1)
    in M21
    =INDEX(I$6:I$24,MATCH(ROW()-20,K$6:K$24,0),1)
    and copy these two formulas down as far as row 25
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    07-04-2017
    Location
    South Africa
    MS-Off Ver
    2016
    Posts
    15

    Re: Top 5 Table

    Perfect, what would I do without you guys.

    Thanks.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: Top 5 Table

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Crosstab table to pivot table to plain table exceeds Excel row limit
    By jjsilva in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-30-2017, 11:43 PM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Macro to fill 1st table with info from 2nd table. 2nd table search with 2 conditions
    By Ribeiro.JD in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2016, 02:03 PM
  4. [SOLVED] Adding a row in a table corrupts formula in a summary table (2nd table)
    By How How in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2015, 02:35 PM
  5. Replies: 1
    Last Post: 07-29-2015, 05:19 PM
  6. [SOLVED] Need a formula to sort a table into table (for a Table Plan) and name in column 2
    By jbpianoman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2012, 09:21 AM
  7. [SOLVED] PIVOT TABLE - Summary Table into a Databasae Table.
    By sansk_23 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2005, 03:06 AM

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