smwashburn
New Member
- Joined
- Mar 1, 2016
- Messages
- 7
Hi All,
I am trying to create a report in excel that has one sheet that ranks criteria from largest to smallest, by referencing a second sheet that stores all the data.
One one sheet I have a list of departments, and their corresponding success rates as a percentage. The current order of the departments on this sheet is simply in numerical order.
On the second sheet I have used an array formula to return all of the percentages in order of largest to smallest, including any duplicates.
On this second sheet I am trying to use an Index/Match formula to generate the corresponding departments. Currently the simple index/match formula I am using will return one department multiple times if the percentage is the same.
Here is the data on sheet one:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Deptartment[/TD]
[TD]Success Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]525[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]527[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]540[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]550[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]551[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]552[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]570[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]580[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Here is what I currently have on sheet 2. I am trying to create a formula that will retrieve the corresponding department without giving me duplicates.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Department[/TD]
[TD]Success Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for any help!
I am trying to create a report in excel that has one sheet that ranks criteria from largest to smallest, by referencing a second sheet that stores all the data.
One one sheet I have a list of departments, and their corresponding success rates as a percentage. The current order of the departments on this sheet is simply in numerical order.
On the second sheet I have used an array formula to return all of the percentages in order of largest to smallest, including any duplicates.
On this second sheet I am trying to use an Index/Match formula to generate the corresponding departments. Currently the simple index/match formula I am using will return one department multiple times if the percentage is the same.
Here is the data on sheet one:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Deptartment[/TD]
[TD]Success Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]525[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]527[/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]540[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]550[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]551[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]552[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]570[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]580[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Here is what I currently have on sheet 2. I am trying to create a formula that will retrieve the corresponding department without giving me duplicates.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Department[/TD]
[TD]Success Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]92[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for any help!