+ Reply to Thread
Results 1 to 12 of 12

List top 5 items from a table

  1. 11-22-2021, 01:26 AM #1
    chaliseyp
    chaliseyp is offline
    Registered User
    Join Date
    08-13-2020
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    78

    List top 5 items from a table

    I want to list the top 5 items from a list based on their dollar value and their corresponding ID + description.

    I use the large function to get the top 5 values. And then lookup function to get the corresponding values from the table.

    The problem is when the values are same (for Risk ID 3 & 6 ), how do i display both items.

    In the desired result, for S.No 3 the Risk ID should be 71 and Risk should be "The IFT design for the delivery method proposed"

    Thanks in advance for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-22-2021, 01:58 AM #2
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: List top 5 items from a table

    add a small number to make a difference for example rownumber/10000
    column F:G ranking in 2 columns.
    Column H ranking in 1 column (spill formula, i Think, has it {}?, so CTRL+SHIFT+Enter instead of Enter in your 2010 version) 
    Please Login or Register  to view this content.
    Then you can use the ranking for your top 5.
    Attached Files Attached Files
    Last edited by bsalv; 11-22-2021 at 02:02 AM.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  3. 11-22-2021, 02:45 AM #3
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: List top 5 items from a table

    Another formula, bigger, but no need for CTRL+SHIFT+Enter and also an alternative of Pivot Table.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-22-2021, 03:14 AM #4
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,306

    Re: List top 5 items from a table

    POWER QUERY
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-22-2021, 03:21 AM #5
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: List top 5 items from a table

    Nice but aggregate and PQ didn't exist in TS's 2010-version, what a pity, unless he/she has an upgrade.
    Reply With QuoteRegister To Reply
  6. 11-22-2021, 03:57 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: List top 5 items from a table

    Quote Originally Posted by bsalv View Post
    Nice but aggregate and PQ didn't exist in TS's 2010-version, what a pity, unless he/she has an upgrade.
    AGGREGATE was introduced in Excel 2010.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  7. 11-22-2021, 04:10 AM #7
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,462

    Re: List top 5 items from a table

    Quote Originally Posted by bsalv View Post
    Nice but aggregate and PQ didn't exist in TS's 2010-version, what a pity, unless he/she has an upgrade.
    And PQ is available to XL2010 as an add-in
    Reply With QuoteRegister To Reply
  8. 11-22-2021, 04:11 AM #8
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: List top 5 items from a table

    my apology, you 're right.
    Reply With QuoteRegister To Reply
  9. 11-22-2021, 05:39 PM #9
    chaliseyp
    chaliseyp is offline
    Registered User
    Join Date
    08-13-2020
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    78

    Re: List top 5 items from a table

    Thanks all for your help.
    Reply With QuoteRegister To Reply
  10. 11-22-2021, 07:16 PM #10
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: List top 5 items from a table

    Quote Originally Posted by bsalv View Post
    Nice but aggregate and PQ didn't exist in TS's 2010-version, what a pity, unless he/she has an upgrade.
    AGGREGATE function
    Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel for Mac 2011 Excel Starter 2010

    https://support.microsoft.com/en-us/...6-e19993fa26df
    Reply With QuoteRegister To Reply
  11. 11-23-2021, 02:33 AM #11
    bsalv
    bsalv is offline
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: List top 5 items from a table

    Aggregate in 2010, I already said so in #8
    Reply With QuoteRegister To Reply
  12. 11-23-2021, 04:43 AM #12
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: List top 5 items from a table

    Helping you not forget this,

    I do mistakes too, feel free to alert me.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract and return a distinct and duplicate items list separately from a two list items
    By Mohammad Munawar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2020, 07:42 AM
  2. [SOLVED] Distribute items evenly into table from a list
    By Robn02 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-02-2019, 05:58 PM
  3. [SOLVED] How to generate a list of duplicated items from a table
    By Banaticus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-04-2017, 06:12 AM
  4. Show items in pivot table filter's drop-down list based on other filters of the same table
    By qwertyu_zxc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  5. Pivot table Query- Top 4 items ignoring selected items from the list
    By Excel Dumbo in forum Excel General
    Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  6. Trying to fill in available data on a list where most items are also in my table
    By MattyNinja in forum Access Tables & Databases
    Replies: 1
    Last Post: 04-12-2010, 04:29 PM
  7. Pivot table.... list of visible items
    By ericlch16 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2007, 01:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1