+ Reply to Thread
Results 1 to 8 of 8

Retrieving top-5 names

  1. #1
    Registered User
    Join Date
    12-23-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    4

    Retrieving top-5 names

    hi - I have a table that pulls up daily trading values for a bunch of stocks in Europe. I am trying to write a function that will retrieve the top-5 traded values and the corresponding name in a separate table on the same sheet i.e. the 5 largest values from column E and the corresponding names from column B. The original sheet pulls data from 2 other applications which need separate log-ins so I'm attaching a sample with this question.
    Thanks in advance!
    Attached Files Attached Files
    Last edited by versuswhat; 02-16-2015 at 02:01 PM. Reason: query solved

  2. #2
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Re: Retrieving top-5 names

    Try:
    in G2:
    =LARGE($E$2:$E$132,ROWS($G$2:G2))
    in H2:
    =INDEX($A$2:$A$132,MATCH(G2,$E$2:$E$132,0))

    or you can make a single formula.. but does not have sence beacuse in better with numbers
    =INDEX($A$2:$A$132,MATCH(LARGE($E$2:$E$132,ROWS($G$2:G2))
    ,$E$2:$E$132,0))

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

    Re: Retrieving top-5 names

    in G1
    =LARGE(C$2:C$132,1)
    in H1
    =INDEX(A$2:A$132,MATCH(G1,C$2:C$132,0),1)

    copy down as far as G5 for the top 5, changing the ,1 in column G to 2 3 4 5

    NOTE: Be aware should there be duplicate values in the top 5 of column C MATCH will find only the first occurrence of the repeated value
    so if the data was

    SMITH LTD 13678.42
    JONES LTD 13678.42

    you'll get

    SMITH LTD 13678.42
    SMITH LTD 13678.42
    Last edited by Special-K; 02-16-2015 at 01:12 PM.
    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
    12-23-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Retrieving top-5 names

    Thanks GerryZucca. That worked to retrieve the biggest value & name. Any ideas on how to get the top 5?
    Also agree it makes more sense with the number

  5. #5
    Registered User
    Join Date
    12-23-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Retrieving top-5 names

    Thanks Special-K. Just trying this now.

  6. #6
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Re: Retrieving top-5 names

    Quote Originally Posted by versuswhat View Post
    Thanks GerryZucca. That worked to retrieve the biggest value & name. Any ideas on how to get the top 5?
    Also agree it makes more sense with the number
    Those are allready the top 5
    for the top 5 amounts with the second formula =INDEX($A$2:$A$132,MATCH(G2,$E$2:$E$132,0)) you'll have the list of top 5
    € 287.450.128,33 ROG.VX
    € 244.504.501,00 CRDI.MI
    € 191.236.008,73 ING.AS
    € 171.020.966,82 ENI.MI
    € 160.899.849,26 TOTF.PA

  7. #7
    Registered User
    Join Date
    12-23-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Retrieving top-5 names

    oh terrific, got it now. Thanks much!

    287450128.3 ROG.VX
    244504501 CRDI.MI
    191236008.7 ING.AS
    171020966.8 ENI.MI
    160899849.3 TOTF.PA

    Meanwhile, method suggested by Special-K above works too.

  8. #8
    Forum Contributor
    Join Date
    02-15-2015
    Location
    Cecoslovacchia
    MS-Off Ver
    365 personal
    Posts
    100

    Re: Retrieving top-5 names

    You are welcome
    and thank you for your 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. Finding and Retrieving Numbers and Names from a Large Workbook
    By disfunctional in forum Excel General
    Replies: 4
    Last Post: 01-02-2015, 04:52 PM
  2. Excel macro for retrieving file names
    By Toadums in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-17-2008, 07:54 PM
  3. [SOLVED] Retrieving Values Saved in Names
    By Steve Drenker in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-03-2006, 11:55 PM
  4. [SOLVED] Retrieving Names of Worksheets
    By Brad Sumner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2005, 02:05 PM
  5. Retrieving Values Using Range Names as References
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 02:16 AM

Tags for this Thread

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