Obtain result based on largest value

[mark9988]

Hello,

I'm hoping you can help me find a solution to the following problem. So here it goes...I have four columns in my data set:

Column A: Customer Name

Column B: Exposure amount of what is past due on the customer's account

Column C: Name of Collector who manages the account

Column D: This is where I need the formula created. Based on my illustration below, Tony's Plumbing has five accounts and there are three collectors managing these accounts. Collector "Mark" manages the most exposure ($2,500) compared to the other collectors.

Since Mark manages the greatest amount, he would be assigned as the master collector to Tony's Plumbing and therefore his name would be populated in the 4th column.


Customer, $ Amount, Collector, Master Collector
Tony's Plumbing, $1,000, Mark, Mark
Tony's Plumbing, $1,200, Kelly, Mark
Tony's Plumbing, $200, Wayne, Mark
Tony's Plumbing, $1,500, Mark, Mark
Tony's Plumbing, $1,500, Wayne, Mark


Thank you all in advance for your help!

Regards,
Mark9988

 

[Desu Nota from Columbus]

In F2 put: =SUMPRODUCT(--($C$2:$C$7=$C2),$B$2:$B$7)

Drag down to F7


In G2 Put:=C2

Drag down to G7


In D2 put:=VLOOKUP(MAX($F$2:$F$7),$F$2:$G$7,2)

Drag down to D7


Does this help? Doesn't decide who to put in case of tie for master collector--first name listed gets it.

ANd this isn't a universal application...there are too many variables for me to help you quickly!

 

[mark9988]

ok, the formula almost works. However, I failed to mention earlier that several customers will reside in column A.

For example, below is an updated illustration of what an actual spreadsheet will entail. You will notice I have included a couple of additional customers in column A.

As a result, the vlookup/max formula in cell D2 would need to differentiate these customers accordingly.

I was able to update the SumProduct formula for cell F2 to account for the several customers. It would look like this I believe:

=SUMPRODUCT(--($A$2:$A$10=$A2),--($C$2:$C$10=$C2), $B$2:$B$10).




Customer, $ Amount, Collector, Master Collector
Tony's Plumbing, $1000, Mark, Mark
Tony's Plumbing, $1200, Kelly, Mark
Tony's Plumbing, $200, Wayne, Mark
Tony's Plumbing, $1500, Mark, Mark
Joe's Pizza, $1000, Kelly, Kelly
Joe's Pizza, $800, Mark, Kelly
Tony's Plumbing, $1500, Wayne, Mark
Mary's Hair Salon, $2000, Wayne, Wayne
Mary's Hair Salon, $600, Kelly, Wayne
Joe's Pizza, $500,Kelly, Kelly


Thanks again,
Mark9988

 

[id107]

Keeping the SumProduct in col F, this should work
=IF(A2="","",INDEX(C$2:C$200,(MATCH(MAX(IF(A$2:A$200=A2,F$2:F$200)),(F$2:F$200)*(A$2:A$200=A2),0)),0))

confirmed with ctlr, shift & enter.

 

[mark9988]

Thank you Desu Nota from Columbus & id107! works like a charm!

 

[mark9988]

Hello once again,

so it appears I ran into a road block...

Within my database, there may be a group of customers listed in column A that do not have any exposure amounts. In other words, the customers have a corresponding value of zero in column B (row 12 and 13 are examples).

If a customer with zero exposure is listed, I would essentially need the collector listed in column C to be assigned as the master collector in column D.

To simplify things, if there are two collectors assigned to a customer with zero exposure, either one can be assigned as the master collector.

In my illustration below, you will notice in row 12 that Wayne is assigned the Master Collector for "John's Coffee". This is incorrect since Kelly is the collector; therefore, I would need Kelly to be selected as the Master Collector.

The same issue is present in row 13 as well...

Here is my formula in cell D12:

{=INDEX(C$2:C$13,(MATCH(MAX(IF(A$2:A$13=A12,SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12), $B$2:$B$13))),SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12), $B$2:$B$13)*(A$2:A$13=A12),0)))}


A breakdown of this formula shows the culprit to this problem appears to be the match function. This formula is shown in cell E12:

{=MATCH(MAX(IF(A$2:A$13=A12,SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12),$B$2:$B$13))),SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12),$B$2:$B$13)*(A$2:A$13=A12),0)}

If I understand correctly, when a zero exposure amount is listed in column B, this match formula returns "1" and the index formula ultimately returns "Wayne" using this example.

Any assistance would greatly be appreciated.

Thanks,
Mark9988




<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Customer</td><td style="font-weight: bold;text-align: center;;"> $ Amount</td><td style="font-weight: bold;text-align: center;;"> Collector</td><td style="font-weight: bold;text-align: center;;"> Master Collector </td><td style="font-weight: bold;text-align: center;;">MATCH REFERENCE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Tony's Plumbing</td><td style="text-align: right;;">1000</td><td style=";">Wayne</td><td style=";">Wayne</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Tony's Plumbing</td><td style="text-align: right;;">1200</td><td style=";"> Kelly</td><td style=";">Wayne</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Tony's Plumbing</td><td style="text-align: right;;">200</td><td style=";"> Wayne</td><td style=";">Wayne</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Tony's Plumbing</td><td style="text-align: right;;">1500</td><td style=";"> Mark</td><td style=";">Wayne</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Joe's Pizza</td><td style="text-align: right;;">1000</td><td style=";"> Kelly</td><td style=";"> Kelly</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Joe's Pizza</td><td style="text-align: right;;">800</td><td style=";"> Mark</td><td style=";"> Kelly</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Tony's Plumbing</td><td style="text-align: right;;">1500</td><td style=";"> Wayne</td><td style=";">Wayne</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Mary's Hair Salon</td><td style="text-align: right;;">2000</td><td style=";"> Wayne</td><td style=";"> Wayne</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Mary's Hair Salon</td><td style="text-align: right;;">600</td><td style=";"> Kelly</td><td style=";"> Wayne</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Joe's Pizza</td><td style="text-align: right;;">500</td><td style=";">Kelly</td><td style=";"> Kelly</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">John's Coffee</td><td style="text-align: right;;">0</td><td style=";">Kelly</td><td style="background-color: #FFC000;;">Wayne</td><td style="text-align: right;background-color: #FFFF00;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Pete's Lumber</td><td style="text-align: right;;">0</td><td style=";">Mark</td><td style="background-color: #FFC000;;">Wayne</td><td style="text-align: right;background-color: #FFFF00;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

 

[Desu Nota from Columbus]

maybe try this formula instead in D12


=IF(B12=0,C12,INDEX(C$2:C$13,(MATCH(MAX(IF(A$2:A$13=A12,SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12), $B$2:$B$13))),SUMPRODUCT(--($A$2:$A$13=$A12),--($C$2:$C$13=$C12), $B$2:$B$13)*(A$2:A$13=A12),0))))

confirmed with Ctrl+Shift+Enter

 

[mark9988]

It looks like it's working, thank you again!