+ Reply to Thread
Results 1 to 11 of 11

Finding the nth value without duplicates?

  1. #1
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Lightbulb Finding the nth value without duplicates?

    Hello,

    I have 3 columns of cells. one has raw data in it. another has the numbers 1 thru 6 and the last has the formula below.

    Essentially the cell with the formula (below), looks at the column of raw data and finds the nth highest (in this case 1st) and then displays the number (from 1 to 6) from the cell next to nth raw data cell.

    Problem is when 2 or more cells have the same number it only displays the first match multiple times.

    The formula I have used is:

    =INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,1),AI125:AI148,0))

    Is there a way to show the first nth match and then the next nth match (even know they are the same) in an order?

    Cheers!!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding the nth value without duplicates?

    =INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,rows($1:1)),AI125:AI148,0)) fill down
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Re: Finding the nth value without duplicates?

    Still wants to show duplicate results...

  4. #4
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Re: Finding the nth value without duplicates?

    AI AJ AO

    0.244 | #1 |=INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,1),AI125:AI148,0)) - which shows #2
    0.312 | #2 |=INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,2),AI125:AI148,0)) - which shows #3
    0.288 | #3 |=INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,3),AI125:AI148,0)) - which shows #1
    0.244 | #4 |=INDEX(AJ125:AJ148,MATCH(LARGE(AI125:AI148,1),AI125:AI148,0)) - which also shows #1 (but i want it to show #4)

    because when I go to 5th highest, #4 ends up getting skipped completely.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding the nth value without duplicates?

    oops misread your original

  6. #6
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Re: Finding the nth value without duplicates?

    Thats ok, any further ideas??

  7. #7
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Finding the nth value without duplicates?

    Hi, try this array formula (need to entered with ctrl+shift+enter)

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  8. #8
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Finding the nth value without duplicates?

    Hi, Ref the attachment
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Re: Finding the nth value without duplicates?

    Many thanks boopathiraja!

    Your first equation worked quite well.

    I also needed it in other areas of column AI and AJ. I tried just adjusting the row numbers in the formula but it didn't seem to work in these other areas.

    any ideas?

  10. #10
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Finding the nth value without duplicates?

    Hi, ref the attachment, can you elaborate where do you facing errors,
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-26-2014
    Location
    Melbourne, Australia
    MS-Off Ver
    excel ( for mac ) 2008
    Posts
    27

    Re: Finding the nth value without duplicates?

    many many thanks boopathiraja! all fixed!

+ 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. Replies: 2
    Last Post: 12-27-2013, 09:24 AM
  2. Finding duplicates
    By tmcfann in forum Excel General
    Replies: 2
    Last Post: 10-04-2013, 04:47 AM
  3. Finding duplicates
    By TBENTON in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-10-2013, 02:55 PM
  4. Help finding duplicates
    By devinm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2009, 03:23 AM
  5. Finding duplicates
    By Ted Metro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2005, 03:15 PM

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