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"

Reason Code Name	Totals
Budget	1
Conflict with other work	0
Customer Issue	0
Distribution Delay	1
Engineering	0
Engineering & Construction Duration	0
Equipment Failure	0
In Service Equipment Failure	0
Material Unavailable	0
Mobile Unavailable	0
Permitting/Easement	0
Pre-Approved Outage Declined	0
Resource Unavailable	0
Telecom Delay	2

<tbody>
</tbody>

TOP3	YTD Totals
Reason Codes	Totals
Telecom Delay	2
Distribution Delay	1
Distribution Delay	1

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Reason Code Name	Totals			TOP3	YTD Totals
2​	Budget	1​			3​	4​
3​	Conflict with other work	0​			Reason Codes	Totals
4​	Customer Issue	0​			Equipment Failure	2​
5​	Distribution Delay	1​			Telecom Delay	2​
6​	Engineering	0​			Budget	1​
7​	Engineering & Construction Duration	0​			Distribution Delay	1​
8​	Equipment Failure	2​
9​	In Service Equipment Failure	0​
10​	Material Unavailable	0​
11​	Mobile Unavailable	0​
12​	Permitting/Easement	0​
13​	Pre-Approved Outage Declined	0​
14​	Resource Unavailable	0​
15​	Telecom Delay	2​

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.