+ Reply to Thread
Results 1 to 2 of 2

Find the address of an n't max value in an unsorted list

Hybrid View

  1. #1
    Registered User
    Join Date
    06-27-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2003
    Posts
    2

    Find the address of an n't max value in an unsorted list

    Hi

    I have a large spreadsheet with a lot of data (roughly A2:CI1250) and I want to find the row number of the n't maximum value in one of the columns. For various reasons I can not sort the values in order so I have to search them as is. Also there is several of the cells that has the same value, why there could be the same value among the 2nd,3rd and 4th biggest value. Over simplified it looks like this :
    A B
    1 Text A 23
    2 Text B 15
    3 Text C 13
    4 Text D 15
    5 Text E 11
    6 Text F 15

    and I would like to find the A column value of the 2nd, 3rd and 4th biggest value in the B column (i.e. Text B, Text D, Text F)

    I have solved it with a really complicated formula using match, address, Large etc. but there must be some easier way to solve this. The biggest issue and the reason why it is becomming a large list is that some of the values are the same and it is an unsorted list. So I can easily find the biggest numbers but when searching for the address of those fields I can only find the address of the first value that is the same (i.e the 2nd etc). For the unsolved but simpler solution I'm using the following formula =INDIRECT(ADDRESS(MATCH(LARGE(B:B;2);B:B;0);COLUMN(B1);4;1)) which for the 2nd biggest value will give the correct "Text B" but when changing the n'value from 2 to 3 then I will still get the result "Text B" (but I would like to find "Text D" instead). The way I have solved it now with the complicated way is to have a IF formula and then checking if the 2nd and 3rd largest number are the same and if it is then I will change the address where I'm looking after the value to be B3:B6 (in the example above) instead of the entire column. This become a very complicated formula since I need to have a very similar parts over and over again in there.

    Is there anyone who have any ideas on how I could shorten the solution and come up with a much more elegant solution?

    Thanks
    /Martin

  2. #2
    Registered User
    Join Date
    06-27-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Find the address of an n't max value in an unsorted list

    Sorry the formating for the table above didn't come out correct but should of course say A centred over "Text A" and B centered over "23"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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