Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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. ## 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. I agreed to these rules
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. ## 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))),""))),"")

5. I agreed to these rules
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)

#### 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