Creating a List in Descending Order with Criteria From Data Dump

[KimboSlice]

Hi all,

Long time lurker, first time poster. I have the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/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]Product[/TD]
[TD]Category[/TD]
[TD]Sales[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Squash[/TD]
[TD]Vegetable[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pineapple[/TD]
[TD]Fruit[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blueberries[/TD]
[TD]Fruit[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]Correct[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Onion[/TD]
[TD]Vegetable[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]Answer[/TD]
[TD]Pineapple[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Banana[/TD]
[TD]Fruit[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Lettuce[/TD]
[TD]Vegetable[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Peas[/TD]
[TD]Vegetable[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Orange[/TD]
[TD]Fruit[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In cells F1, F2, and F3, I want to create a list of the 3 Products with the most sales, from most to least sales, that also match the criteria in cell E1, "Fruit". So in this instance, I would see F1 = Apple, F2 = Pineapple, and F3 = Orange.

The data dump is "redumped" quite frequently, so I would prefer to not use helper columns.

My apologies in advance if this formula question has already been discussed. I've been able to find solutions to parts of it on the internet, but not one that puts it all together.

Any help is greatly appreciated!

Thanks!

 

[DRSteele]

Welcome to the Forum.

May I suggest that you watch a video on the ExcelIsFun channel on youtube? It think it does exactly what you want using formulas. It considers ties and duplicates, so it is robust enough. If it suits your purposes, try out the techniques - you can even download the file associated with the video and conduct experiments.

Let us know.

https://www.youtube.com/watch?v=rKDI-kdBsjY

 

[FranzV]

That formula works as long as there are no ties within the Top 3. Let me know if you need a formula that will work even with ties, I have done it before.

I used named ranges for easier reading.

[TABLE="class: head"]
<tbody>[TR="bgcolor: #888888"]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[/TR]
[TR]
[TD="bgcolor: #888888"]

1​

[/TD]
[TD]Product[/TD]
[TD]Category[/TD]
[TD]Sales[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[TD]{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

2​

[/TD]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

3​

[/TD]
[TD]Squash[/TD]
[TD]Vegetable[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

4​

[/TD]
[TD]Pineapple[/TD]
[TD]Fruit[/TD]
[TD]

90​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

5​

[/TD]
[TD]Blueberries[/TD]
[TD]Fruit[/TD]
[TD]

20​

[/TD]
[TD][/TD]
[TD]Correct[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

6​

[/TD]
[TD]Onion[/TD]
[TD]Vegetable[/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD]Answer[/TD]
[TD]Pineapple[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

7​

[/TD]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

8​

[/TD]
[TD]Banana[/TD]
[TD]Fruit[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

9​

[/TD]
[TD]Lettuce[/TD]
[TD]Vegetable[/TD]
[TD]

80​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

10​

[/TD]
[TD]Peas[/TD]
[TD]Vegetable[/TD]
[TD]

70​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #888888"]

11​

[/TD]
[TD]Orange[/TD]
[TD]Fruit[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Values

	A	B	C	D	E	F
Product	Category	Sales	Fruit	Apple
Carrot	Vegetable	Pineapple
Squash	Vegetable	Orange
Pineapple	Fruit
Blueberries	Fruit	Correct	Apple
Onion	Vegetable	Answer	Pineapple
Apple	Fruit	Orange
Banana	Fruit
Lettuce	Vegetable
Peas	Vegetable
Orange	Fruit

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"] 10 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"] 40 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] 90 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] 20 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] 60 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] 100 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"] 30 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"] 80 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"] 70 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"] 50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD]{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Categories[/TH]
[TD]=Hoja4!$B$2:$B$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Products[/TH]
[TD]=Hoja4!$A$2:$A$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Sales[/TH]
[TD]=Hoja4!$C$2:$C$11[/TD]
[/TR]
</tbody>[/TABLE]
[/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][/tr][tr][td]

1​

[/td][td]Product[/td][td]Category[/td][td]Sales[/td][td]Fruit[/td][/tr]
[tr][td]

2​

[/td][td]Carrot[/td][td]Vegetable[/td][td]

10​

[/td][td]

3​

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

3​

[/td][td]Squash[/td][td]Vegetable[/td][td]

40​

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

4​

[/td][td]Pineapple[/td][td]Fruit[/td][td]

90​

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

5​

[/td][td]Blueberries[/td][td]Fruit[/td][td]

20​

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

6​

[/td][td]Onion[/td][td]Vegetable[/td][td]

60​

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

7​

[/td][td]Apple[/td][td]Fruit[/td][td]

100​

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

8​

[/td][td]Banana[/td][td]Fruit[/td][td]

30​

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

9​

[/td][td]Lettuce[/td][td]Vegetable[/td][td]

80​

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

10​

[/td][td]Peas[/td][td]Vegetable[/td][td]

70​

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

11​

[/td][td]Orange[/td][td]Fruit[/td][td]

50​

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

12​

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

In D2 just enter:

=MIN(3,COUNTIFS($B$2:$B$11,D$1))

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

=IF(ROWS(D$3:D3)<=D$2,INDEX($A$2:$A$11,SMALL(IF(C$2:C$11=LARGE(IF($B$2:$B$11=$D$1,C$2:C$11),ROWS($D$3:D3)),ROW(C$2:C$11)-ROW(C$2)+1),
SUM(IF(LARGE(IF($B$2:$B$11=$D$1,C$2:C$11),ROW(C$2:C2)-ROW($C$2)+1)=LARGE(IF($B$2:$B$11=$D$1,C$2:C$11),ROWS(D$3:D3)),1)))),"")

Note that the set up does not aggregate multiple occurrences of a product if multiple occurrence is possible.