Sortby with multiple entries and columns

[osciva]

Hi,

Looking for help with a formula... I assume a combination of Sortby, Unique, Filter...? My scenariois similar to this: I want to output a unique, sorted and filtered list of the top selling fruits in Sweden, from a table similar to this

A (fruit)	B (country)	C (sold quantity)
Apple	Sweden	10
Apple	Norway	5
Apple	Sweden	4
Pear	Sweden	4
Pear	Sweden	2
Pear	Norway	8
Orange	Sweden	5
Orange	Sweden	25
Banana	Norway	10

I.e. the output I want to have from my formula is:

Orange

Apple

Pear

Thanks.

 

[RoryA]

Assuming you want to sort based on the aggregated totals, a pivot table would seem a simple solution.

 

[osciva]

Assuming you want to sort based on the aggregated totals, a pivot table would seem a simple solution.

Thanks, and correct, I want to sort based on the aggregated totalts. However, a pivot table isn't suitable since this will be a part of an excel dashboard with specific layout.

 

[RoryA]

It's still not clear how a pivot table wouldn't suit, but I'd at least use one as a summary, then your dashboard can simply source the list from that if need be.

 

[kweaver]

Not necessarily a solution to your problem (which I found interesting). I played around with it, first by sorting the original data in columns A-C per the example below.

Book2
	A	B	C	D	E	F	G	H	I	J
1	A (fruit)	B (country)	C (sold quantity)		X-Country
2	Pear	Norway	25		Mango	Mango		Mango	X-Country	35
3	Banana	Norway	10		Pear	Pear		Pear	X-Country	25
4	Apple	Norway	5			Raspberry		Mango	X-Country	10
5	Orange	Sweden	25		Raspberry			Raspberry	X-Country	5
6	Apple	Sweden	10
7	Orange	Sweden	5
8	Apple	Sweden	4
9	Pear	Sweden	4
10	Pear	Sweden	2
11	Mango	X-Country	35
12	Pear	X-Country	25
13	Mango	X-Country	10
14	Raspberry	X-Country	5
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=UNIQUE(FILTER(E2:E14,E2:E14<>""))
H2:J5	H2	=FILTER(A2:C14,E1=B2:B14)
E2:E14	E2	=(IF(COUNTIF(H$2:H2,H2)=1,H2,""))
Dynamic array formulas.

 

[Fluff]

Another option if you have the LET function

+Fluff 1.xlsm
	A	B	C	D	E	F
1	A (fruit)	B (country)	C (sold quantity)			Sweden
2	Apple	Sweden	10			Orange
3	Apple	Norway	5			Apple
4	Apple	Sweden	4			Pear
5	Pear	Sweden	4
6	Pear	Sweden	2
7	Pear	Norway	8
8	Orange	Sweden	5
9	Orange	Sweden	25
10	Banana	Norway	10
11
Master

Cell Formulas
Range	Formula
F2:F4	F2	=LET(uni,UNIQUE(A2:A10),sm,SUMIFS(C2:C10,B2:B10,F1,A2:A10,uni),SORTBY(FILTER(uni,sm>0),FILTER(sm,sm>0),-1))
Dynamic array formulas.

 

[osciva]

Another option if you have the LET function

+Fluff 1.xlsm
	A	B	C	D	E	F
1	A (fruit)	B (country)	C (sold quantity)			Sweden
2	Apple	Sweden	10			Orange
3	Apple	Norway	5			Apple
4	Apple	Sweden	4			Pear
5	Pear	Sweden	4
6	Pear	Sweden	2
7	Pear	Norway	8
8	Orange	Sweden	5
9	Orange	Sweden	25
10	Banana	Norway	10
11
Master

Cell Formulas
Range	Formula
F2:F4	F2	=LET(uni,UNIQUE(A2:A10),sm,SUMIFS(C2:C10,B2:B10,F1,A2:A10,uni),SORTBY(FILTER(uni,sm>0),FILTER(sm,sm>0),-1))
Dynamic array formulas.

Thanks, that worked perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback.