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:
<tbody>
</tbody>
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.
<tbody>
</tbody>
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:
A | B | |
1 | Deptartment | Success Rate |
2 | 525 | 83 |
3 | 527 | 92 |
4 | 540 | 77 |
5 | 550 | 60 |
6 | 551 | 77 |
7 | 552 | 0 |
8 | 570 | 15 |
9 | 580 | 0 |
<tbody>
</tbody>
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.
A | B | |
1 | Department | Success Rate |
2 | 92 | |
3 | 83 | |
4 | 77 | |
5 | 77 | |
6 | 60 | |
7 | 15 | |
8 | 0 | |
9 | 0 |
<tbody>
</tbody>
Thank you in advance for any help!