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:

	A	B	C	D	E	F
1	Product	Category	Sales		Fruit
2	Carrot	Vegetable	10
3	Squash	Vegetable	40
4	Pineapple	Fruit	90
5	Blueberries	Fruit	20		Correct	Apple
6	Onion	Vegetable	60		Answer	Pineapple
7	Apple	Fruit	100			Orange
8	Banana	Fruit	30
9	Lettuce	Vegetable	80
10	Peas	Vegetable	70
11	Orange	Fruit	50

<tbody>
</tbody>

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.

	A	B	C	D	E	F
1​	Product	Category	Sales		Fruit	{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}
2​	Carrot	Vegetable	10​			{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}
3​	Squash	Vegetable	40​			{=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )}
4​	Pineapple	Fruit	90​
5​	Blueberries	Fruit	20​		Correct	Apple
6​	Onion	Vegetable	60​		Answer	Pineapple
7​	Apple	Fruit	100​			Orange
8​	Banana	Fruit	30​
9​	Lettuce	Vegetable	80​
10​	Peas	Vegetable	70​
11​	Orange	Fruit	50​

<tbody>
</tbody>

Values

	A	B	C	D	E	F
1	Product	Category	Sales		Fruit	Apple
2	Carrot	Vegetable	10			Pineapple
3	Squash	Vegetable	40			Orange
4	Pineapple	Fruit	90
5	Blueberries	Fruit	20		Correct	Apple
6	Onion	Vegetable	60		Answer	Pineapple
7	Apple	Fruit	100			Orange
8	Banana	Fruit	30
9	Lettuce	Vegetable	80
10	Peas	Vegetable	70
11	Orange	Fruit	50

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

Array Formulas Cell Formula F1 {=INDEX( Products, MATCH( LARGE((Categories=$E$1)*Sales,ROW()-ROW($E$1)+1), Sales, 0 ) )} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To Categories =Hoja4!$B$2:$B$11 Products =Hoja4!$A$2:$A$11 Sales =Hoja4!$C$2:$C$11 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​
1​	Product	Category	Sales	Fruit
2​	Carrot	Vegetable	10​	3​
3​	Squash	Vegetable	40​	Apple
4​	Pineapple	Fruit	90​	Pineapple
5​	Blueberries	Fruit	20​	Orange
6​	Onion	Vegetable	60​
7​	Apple	Fruit	100​
8​	Banana	Fruit	30​
9​	Lettuce	Vegetable	80​
10​	Peas	Vegetable	70​
11​	Orange	Fruit	50​
12​

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.