Extract Largest Values with Conditions

[Graemea]

Hi,

I have sample data in A:E and would like to extract results to H:K.

I would like to create a formula that will extract the product names in column H. Lookups will return the values in I:J.

The conditions to return the product names are:

The top 5 products in each Region by Sales that have a positive (including zero) Q1 Change value and an associated negative Q2 Change, or vice-versa, value.

So, in Europe, products AAA, BBB and DDD have switched between negative and non-negative values between Q1 and Q2, and DDD has the highest level of Sales, followed by BBB and then AAA (400 vs 200 vs 100)

Can someone please suggest a formula to do this?

Thanks!

 

[lrobbo314]

Here is a way using Power Query.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	PRODUCT	REGION	SALES	Q1 CHANGE	Q2 CHANGE		PRODUCT	REGION	SALES	Q1 CHANGE	Q2 CHANGE
2	AAA	EUROPE	100	-2.31	0		DDD	EUROPE	400	-1.04	0.8
3	BBB	EUROPE	200	-3.09	2.31		BBB	EUROPE	200	-3.09	2.31
4	CCC	EUROPE	300	-2.91	-3.82		AAA	EUROPE	100	-2.31	0
5	DDD	EUROPE	400	-1.04	0.8
6	EEE	EUROPE	500	3.68	3.18
7	FFF	ASIA	600	4.51	-4.42
8	GGG	ASIA	700	-2.35	-1.25
9	HHH	ASIA	800	4.01	1.41
10	III	ASIA	900	3.97	-0.65
11	JJJ	ASIA	1000	0.79	4.86
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TYPES = Table.TransformColumnTypes(Source,{{"PRODUCT", type text}, {"REGION", type text}, {"SALES", Int64.Type}, {"Q1 CHANGE", type number}, {"Q2 CHANGE", type number}}),
    SELECT = Table.SelectRows(TYPES, each _[Q1 CHANGE] <0 and _[Q2 CHANGE] >=0),
    SORT = Table.Sort(SELECT,{{"SALES", Order.Descending}})
in
    SORT

 

[Graemea]

That's great, thank you!

Currently I don't have access to Power Query - does anyone know a way to do this with an (array?) formula please?

Thanks!

 

[KRice]

Here is another option using formulas. This assumes that you are populating column G (Region) with a region.

MrExcel_20200618.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Product	Region	Sales	Q1 change	Q2 change		Region	Product	Sales	Q1 change	Q2 change
2	AAA	Europe	100	-2.31	0		Asia	III	900	3.97	-0.65
3	BBB	Europe	200	-3.09	2.31		Asia	FFF	600	4.51	-4.42
4	CCC	Europe	300	-2.91	-3.82		Asia
5	DDD	Europe	400	-1.04	0.8		Asia
6	EEE	Europe	500	3.68	3.18		Asia
7	FFF	Asia	600	4.51	-4.42
8	GGG	Asia	700	-2.35	-1.25
9	HHH	Asia	800	4.01	1.41
10	III	Asia	900	3.97	-0.65
11	JJJ	Asia	1000	0.79	4.86
12
Graemea

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(INDEX(A$2:A$11,AGGREGATE(14,6,(ROW($C$2:$C$11)-ROW($C$1))/(($C$2:$C$11=$I2)*($B$2:$B$11=$G2)),1)),"")
I2:I7	I2	=IFERROR(AGGREGATE(14,6,($C$2:$C$11)/(((SIGN($D$2:$D$11)>=0)<>(SIGN($E$2:$E$11)>=0))*($B$2:$B$11=G2)),ROWS(I$2:I2)),"")
J2:K7	J2	=IFERROR(INDEX(D$2:D$11,AGGREGATE(14,6,(ROW($C$2:$C$11)-ROW($C$1))/(($C$2:$C$11=$I2)*($B$2:$B$11=$G2)),1)),"")

 

[lrobbo314]

Another formula option. This will probably run into issues if 2 products have the exact same sales amounts though.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	PRODUCT	REGION	SALES	Q1 CHANGE	Q2 CHANGE	TF			Product	Region	Sales	Q1 Change	Q2 Change
2	AAA	EUROPE	100	-2.31	0	TRUE			DDD	EUROPE	400	-1.04	0.8
3	BBB	EUROPE	200	-3.09	2.31	TRUE			BBB	EUROPE	200	-3.09	2.31
4	CCC	EUROPE	300	-2.91	-3.82	FALSE			AAA	EUROPE	100	-2.31	0
5	DDD	EUROPE	400	-1.04	0.8	TRUE
6	EEE	EUROPE	500	3.68	3.18	FALSE
7	FFF	ASIA	600	4.51	-4.42	FALSE
8	GGG	ASIA	700	-2.35	-1.25	FALSE
9	HHH	ASIA	800	4.01	1.41	FALSE
10	III	ASIA	900	3.97	-0.65	FALSE
11	JJJ	ASIA	1000	0.79	4.86	FALSE
Sheet1

Cell Formulas
Range	Formula
I2:I6	I2	=IF(K2="","",INDEX(Table1[PRODUCT],MATCH($K2,Table1[SALES],0)))
J2:J6	J2	=IF(K2="","",INDEX(Table1[REGION],MATCH($K2,Table1[SALES],0)))
K2:K6	K2	=IF(LARGE((Table1[TF]=TRUE)*Table1[SALES],ROW(A1))>0,LARGE((Table1[TF]=TRUE)*Table1[SALES],ROW(A1)),"")
L2:L5	L2	=IF(K2="","",INDEX(Table1[Q1 CHANGE],MATCH(K2,Table1[SALES],0)))
M2:M5	M2	=IF(K2="","",INDEX(Table1[Q2 CHANGE],MATCH(L2,Table1[Q1 CHANGE],0)))
F2:F11	F2	=AND([@[Q1 CHANGE]]<0,[@[Q2 CHANGE]]>=0)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[KRice]

Here is another option that relies on some helper cells (see col M:O) to establish a unique list of Regions and the number of results that will satisfy the conditions established. The helper cells are used to construct column G in the results table. This set of formulas finds the largest Sales amounts, even duplicates, and correctly reports matching data for Product and Q1/Q2 changes. The only downside is that it does not sort the Sales figures, but instead reports them from greatest row number to least row number. A separate sort operation could be performed on the table afterward if order is important...a two-level sort...the first level sort on the Region (to keep each region together) and the second-level sort on the Sales.

MrExcel_20200618.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Region	Sales	Q1 change	Q2 change		Region	Product	Sales	Q1 change	Q2 change		Regions	Count	Cumul Ct
2	AAA	Europe	600	-2.31	0		Europe	DDD	600	-1.04	0.8		Europe	3	3
3	BBB	Europe	800	-3.09	2.31		Europe	BBB	800	-3.09	2.31		Asia	4	7
4	CCC	Europe	300	-2.91	-3.82		Europe	AAA	600	-2.31	0
5	DDD	Europe	600	-1.04	0.8		Asia	KKK	1000	4.01	-4.86
6	EEE	Europe	500	3.68	3.18		Asia	III	700	3.97	-0.65
7	FFF	Asia	800	4.51	-4.42		Asia	HHH	600	4.01	-1.41
8	GGG	Asia	900	-2.35	-1.25		Asia	FFF	800	4.51	-4.42
9	HHH	Asia	600	4.01	-1.41
10	III	Asia	700	3.97	-0.65
11	JJJ	Asia	500	0.79	4.86
12	KKK	Asia	1000	4.01	-4.86
13	LLL	Asia	700	0.79	4.86
Graemea

Cell Formulas
Range	Formula
G2:G11	G2	=INDEX($M$2:$M$5,AGGREGATE(15,6,(ROW($O$2:$O$5)-ROW($O$1))/($O$2:$O$5>=ROWS(G$2:G2)),1))
H2:H11	H2	=IFERROR(INDEX($A$2:$A$13,AGGREGATE(14,6,($B$2:$B$13=$G2)/($B$2:$B$13=$G2)*(ROW($B$2:$B$13)-ROW($B$1))/((SIGN($D$2:$D$13)>=0)<>(SIGN($E$2:$E$13)>=0)),COUNTIF($G$2:$G2,$G2))),"")
I2:I11	I2	=IFERROR(INDEX($C$2:$C$13,AGGREGATE(14,6,($B$2:$B$13=$G2)/($B$2:$B$13=$G2)*(ROW($B$2:$B$13)-ROW($B$1))/((SIGN($D$2:$D$13)>=0)<>(SIGN($E$2:$E$13)>=0)),COUNTIF($G$2:$G2,$G2))),"")
J2:J11	J2	=IFERROR(INDEX($D$2:$D$13,AGGREGATE(14,6,($B$2:$B$13=$G2)/($B$2:$B$13=$G2)*(ROW($B$2:$B$13)-ROW($B$1))/((SIGN($D$2:$D$13)>=0)<>(SIGN($E$2:$E$13)>=0)),COUNTIF($G$2:$G2,$G2))),"")
K2:K11	K2	=IFERROR(INDEX($E$2:$E$13,AGGREGATE(14,6,($B$2:$B$13=$G2)/($B$2:$B$13=$G2)*(ROW($B$2:$B$13)-ROW($B$1))/((SIGN($D$2:$D$13)>=0)<>(SIGN($E$2:$E$13)>=0)),COUNTIF($G$2:$G2,$G2))),"")
M2:M7	M2	=IFERROR(INDEX(B2:B13,MATCH(0,COUNTIF($M$1:M1,$B$2:$B$13),0)),"")
N2:N7	N2	=IF(M2="","",MIN(COUNT(($C$2:$C$13)/(((SIGN($D$2:$D$13)>=0)<>(SIGN($E$2:$E$13)>=0))*($B$2:$B$13=M2))),5))
O2:O7	O2	=IF(M2="","",SUM(N$2:N2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.