Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Return cell value based on neighboring maximum cell value...

  1. #1
    Join Date
    4th February 2012
    Posts
    3

    Return cell value based on neighboring maximum cell value...

    Hello, I know that from the title this may sound like a redundant post but I have not been able to find a solution that fits my problem, yet. I have a data set that hierarchically arranged such that there are lots of repeating values as one moves from the left most column right like in the following schematic:

    a b d
    a b e
    a c f

    The problem I have is as follows. I have a three columned dataset where, for every unique (repeating) value in column one, I need the entire row of values that corresponds to the maximum in column three (the question will become more clear when looking at the dataset.) A simple diagram that hopefully illustrates this problem is below where the idealized data set is on the left and the output I'm looking for on the right:

    10-4-15 10-6-50
    10-5-35 20-8-60
    10-6-50
    20-7-15
    20-8-60
    20-9-25

    As in the example above, in the real dataset the numbers in the third column sum to 100 (percentages...). So, just to be as clear as I can, for every unique value in the first column, I need to choose the maximum value in column three and return the entire corresponding row. I need to keep the row together as columns one and two are used to link other tables together in a database. I hope this description of the problem made sense, I'm sure its clearer with the actual data. Thanks, SRF

    Excel_Help.xlsx

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,572

    Re: Return cell value based on neighboring maximum cell value...

    In rows 12 and 13 of your sample file, the maximums are repeated. what would be the output in this case

  3. #3
    Join Date
    4th February 2012
    Posts
    3

    Re: Return cell value based on neighboring maximum cell value...

    Thanks for your reply! In the case of a tie, taking the first record is fine. For example, there is a tie for the first record "1017218" in column 3 of "20". A suitable output for that record would be:

    "1017218" "1017218:991195" "20".

    The correct return for the second unique value in column one "1390208" would be:

    "1390208" "1390208:1328690" "17"

    I hope that answers your question. Thanks again for your quick response.

    Cheers,

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st March 2010
    Location
    God's Own Country
    Posts
    3,572

    Re: Return cell value based on neighboring maximum cell value...

    Try this as array formula and drag down....

    D2=IFERROR(INDEX($A$2:$A$55,SMALL(IF($A$2:$A$55<>OFFSET($A$2:$A$55,1,0),ROW(INDIRECT("1:"&ROWS($A$2:$A$55))),""),ROW()-1)),"")

    This one drag down and one column across

    E2=IF($D2<>"",INDEX(B$2:B$55,MIN(IF(($A$2:$A$55=$D2)*($C$2:$C$55=MAX(($A$2:$A$55=$D2)*($C$2:$C$55))),ROW(INDIRECT("1:"&ROWS($A$2:$A$55))),""))),"")
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    4th February 2012
    Posts
    3

    Re: Return cell value based on neighboring maximum cell value...

    SMC, your script worked perfectly! The table I had to work with was over 100,000 rows and it took a bit but worked like a charm. Thanks again, I appreciate the prompt help.

    Cheers,

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: September 17th, 2013, 01:27
  2. Replies: 5
    Last Post: February 22nd, 2011, 09:47
  3. Replies: 5
    Last Post: January 11th, 2011, 23:29
  4. Return Cell Address With Maximum Value
    By moneyshot1 in forum EXCEL HELP
    Replies: 2
    Last Post: March 13th, 2008, 03:10
  5. Replies: 5
    Last Post: November 18th, 2007, 06:00

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
  •  
porno