Create list based on descending values and include duplicates

user_4934150 0
Viewed 48

Excel-File

   |      A       |        B          |    C   |       D         |      E     |
---|--------------|-------------------|--------|-----------------|------------|----
1  |    Product   |       Sales       |        |     Product     |    Sales   | 
---|--------------|-------------------|--------|-----------------|------------|----
2  |   Product_A  |        20         |        |     Product_D   |     100    |
3  |   Product_A  |        10         |        |     Product_D   |      90    |
4  |   Product_A  |        50         |        |     Product_B   |      80    |
5  |   Product_B  |        80         |        |     Product_A   |      50    |
6  |   Product_C  |        40         |        |     Product_D   |      50    |
7  |   Product_C  |        30         |        |     Product_D   |      50    |
8  |   Product_D  |       100         |        |     Product_C   |      40    |
9  |   Product_D  |        90         |        |     Product_C   |      30    |
10 |   Product_D  |        50         |        |     Product_A   |      20    |
11 |   Product_D  |        50         |        |     Product_D   |      10    |
12 |              |                   |        |                 |            |

In Column A I have list of different products with their corresponding sales in Column B.

  1. Products can appear mutliple times in the list.
  2. Sales numbers can be equal for multiple products.

Now, I want to create a descending list of the products in Column D and Column E depending on the sales in Column B.
I tried to go with =LARGE(B3:B12,1), =LARGE(B3:B12,2), ... but it delets all duplicates from the list.

Do you have any idea what formular I need to make this list work?

excel
2 Answers

Office 356 Solution:

=SORT(A2:B11,2,-1,FALSE)

Including Filters:

=SORT(FILTER(A2:B11,A2:A11=M2,""),2,-1,FALSE)

Including multiple Filters-Criterias:

=SORT(FILTER(A2:B11,(A2:A11=M2)*(A2:A11=M2)*(A2:A11=M2),""),2,-1,FALSE)
user_49341500

Here is a solution that should work in older versions.

I create a Table and am using structured references, for flexibility, but you can convert to regular addressing if you need to:

D2: =INDEX(Table31[Product],AGGREGATE(15,6,1/(Table31[Sales]=E2)*ROW(Table31[Sales]),COUNTIF($E$2:E2,E2))-ROW(Table31[#Headers]))
E2: =LARGE(Table31[Sales],ROWS($A$1:A1))

Selectd D2:E2 and fill down as far as needed

enter image description here

user_28729220
Related