+ Reply to Thread
Results 1 to 8 of 8

Return LARGE with tiebreakers and title from result

  1. 08-15-2016, 11:21 AM #1
    augr
    augr is offline
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Return LARGE with tiebreakers and title from result

    Hello,

    I hope this makes sense.

    I am trying to return the LARGE numbers from an array while keeping tiebreaker results in tact and moving onto the next result.

    Here are the formulas that seem to work but I am having trouble with the tiebreakers.

    This array returns the LARGE result and moves on to the next result even if duplicate. Seems to work but I'm having a hard time validating.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This array returns the header title for the LARGE result. Does not return the correct header.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Following is a sample spreadsheet of how the data looks.

    Thank you!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-15-2016, 11:52 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Return LARGE with tiebreakers and title from result

    Your formulae do not tally with your data or your data layout,. Can you re-check and re-post?
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 08-15-2016, 02:14 PM #3
    augr
    augr is offline
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Return LARGE with tiebreakers and title from result

    Sorry about that.

    Attached is the revised spreadsheet with the formula arrays included.

    As you can see my tiebreakers don't return the correct header title.

    Cheers!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 08-16-2016, 11:08 AM #4
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    Re: Return LARGE with tiebreakers and title from result

    This solution makes use of a helper table B7:H10, which could be hidden for aesthetic purposes, that utilizes the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The most objects table is then populated using the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The header name table is then populated using the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  5. 08-16-2016, 11:31 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Return LARGE with tiebreakers and title from result

    Or this array formula in O2, copied across & down, with no helper column:

    =IFERROR(INDEX($B$1:$H$1,SMALL(IF($B2:$H2=J2,COLUMN($B2:$H2)-1,""),1+COUNTIF($I$2:I2,J2))),"")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 08-16-2016, 02:16 PM #6
    augr
    augr is offline
    Registered User
    Join Date
    06-09-2014
    Location
    Hawaii
    MS-Off Ver
    Office 365
    Posts
    75

    Re: Return LARGE with tiebreakers and title from result

    Thank you gents! Both solutions seem to work.
    Reply With QuoteRegister To Reply
  7. 08-16-2016, 02:18 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Return LARGE with tiebreakers and title from result

    You're welcome and thanks for the Reputation.
    Reply With QuoteRegister To Reply
  8. 08-16-2016, 03:10 PM #8
    Czeslaw
    Czeslaw is online now
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,291

    Re: Return LARGE with tiebreakers and title from result

    For Excel 2010+ offers a simpler option.
    Attached Files Attached Files
    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. Breaking Tiebreakers
    By bbobes327 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-26-2014, 04:21 PM
  2. Returning the column title text that matches the value of the resulting large function.
    By jonnydud in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-03-2014, 08:16 AM
  3. Filtering a large range of data to return only row title and cell value
    By adamhirst in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2013, 08:47 PM
  4. Ranking Tiebreakers
    By Confused9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2010, 08:29 AM
  5. Large SumProduct Array -- original title:Loop Function
    By Divius in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-21-2007, 11:25 AM
  6. excel result return wrong calcuation result
    By garyww in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2006, 06:20 AM
  7. Advanced formula - Return result & Show Cell Reference of result
    By Irv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2006, 10:40 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