Top 3 largest values including and identifiying duplicates in order to pull-in their associated names

[jdunbarjr]

I hope that I can explain this clearly. It seems straight forward, but for the life of me, I have spent already 5 hours trying to figure this out. I have a full list of 14 Reason Codes along with their values. Basically, I need to find the TOP 3 (largest) Reason Code Totals (values) from AJ42:AJ55 and add this results in the range AN42:AN45, respectfully, and then pull-in the associated Reason Code Names for each into range "AM42:AM45", respectfully. I am currently using forulas, "=LARGE(AJ42:AJ55,1)" in cell AN42, and changing the Large function Nth value from 1 to 2, and to 3 for the additional fields AN43 and AN44. I am currently using "{=INDEX($K$42:$K$55,MATCH(1,MATCH($AJ$42:$AJ$55,AN42,0),1))}" in cell AM42, and adjusting this formulas for the remaining cells AM43 and AM44. The issue that I have is that there are two Reason Codes Total values that are the same. They both have a value of "1". It does not matter if there are duplicates, because there are going to be duplicate totals, but I need to make sure that it is pulling the next value and not the duplicate value from the same row. Additinoally, as you can see below, in the TOP 3 Reason Code Name table, the name "Distribution Delay" is being pulled-in twice, instead of 1 time along with "Budget", which has the same total value. I cannot tell if it is the formulas that I am using in range: "AM42:AM44" or if the the formulas I am using in range "AN42:AN44" is causing this issue... or both. I guess what I need is after the first total value of a duplicate is found (i.e. AM42), I need the formula in the next rows (AM43 or AM44), to continue the search for the TOP 3 largest Reason Code Total values after the last occurrance/instance of the duplicate value, same thing goes for the Reason Code Names. I hope I explained it well enough and it was not confusing.

Full List:
Reason Codes Names are in range: "K42:K55"
Reason Codes Totals are in range: "AJ42:AJ55"

Top 3:
Top 3 Reason Codes Names are in range: "AM42:AM44"
Top 3 Reason Code Totals are in range: "AN42:AN44"



[TABLE="width: 500"]
<tbody>[TR]
[TD]Reason Code Name[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]Budget[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Conflict with other work[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer Issue[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Distribution Delay[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Engineering[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Engineering & Construction Duration[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Equipment Failure[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]In Service Equipment Failure[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Material Unavailable[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Mobile Unavailable[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Permitting/Easement[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Pre-Approved Outage Declined[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Resource Unavailable[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Telecom Delay[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]TOP3[/TD]
[TD]YTD Totals[/TD]
[/TR]
[TR]
[TD]Reason Codes[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]Telecom Delay[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Distribution Delay[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Distribution Delay[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]Reason Code Name[/td][td]Totals[/td][td][/td][td][/td][td]TOP3[/td][td]YTD Totals[/td][/tr]
[tr][td]

2​

[/td][td]Budget[/td][td]

1​

[/td][td][/td][td][/td][td]

3​

[/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td]Conflict with other work[/td][td]

0​

[/td][td][/td][td][/td][td]Reason Codes[/td][td]Totals[/td][/tr]
[tr][td]

4​

[/td][td]Customer Issue[/td][td]

0​

[/td][td][/td][td][/td][td]Equipment Failure[/td][td]

2​

[/td][/tr]
[tr][td]

5​

[/td][td]Distribution Delay[/td][td]

1​

[/td][td][/td][td][/td][td]Telecom Delay[/td][td]

2​

[/td][/tr]
[tr][td]

6​

[/td][td]Engineering[/td][td]

0​

[/td][td][/td][td][/td][td]Budget[/td][td]

1​

[/td][/tr]
[tr][td]

7​

[/td][td]Engineering & Construction Duration[/td][td]

0​

[/td][td][/td][td][/td][td]Distribution Delay[/td][td]

1​

[/td][/tr]
[tr][td]

8​

[/td][td="bgcolor:#FCE4D6"]Equipment Failure[/td][td="bgcolor:#FCE4D6"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]In Service Equipment Failure[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Material Unavailable[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Mobile Unavailable[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Permitting/Easement[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]Pre-Approved Outage Declined[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]Resource Unavailable[/td][td]

0​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]Telecom Delay[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 just enter:

=COUNTIFS($B$2:$B$15,">="&LARGE($B$2:$B$15,MIN(E2,COUNT($B$2:$B$15))))

Perhaps a better option is:

=COUNTIFS($B$2:$B$15,">="&LARGE($B$2:$B$15,MIN(E2,COUNTIFS($B$2:$B$15,">0"))))

In E4 control+shift+enter, not just enter, and copy down:

=IF($F4="","",INDEX($A$2:$A$15,SMALL(IF($B$2:$B$15=$F4,ROW($A$2:$A$15)-ROW($A$2)+1),COUNTIFS($F$4:F4,F4))))

In F4 just enter and copy down:

=IF(ROWS($F$4:F4)>$F$2,"",LARGE($B$2:$B$15,ROWS($F$4:F4)))

 

[jdunbarjr]

Aladin... you da man!!! Thank you very much. This works PERFECTLY!!!

 

[Aladin Akyurek]

@jdunbarjr

You are welcome.