Index sortby Filter

[barney_t]

Hi
I am having difficulty trying to complete a formula which involves extracting data from a two separate tables to return a product number.

The objective is to return the products with the greatest variance in units to stock. (I am looking to have the top 10 products where the units are greater than the stock)

I have the following formula which gives the required results but does not put them in correct order. I believe the issue may be with the sortby .... FILTER(J2:J11-D2:D11, ?

=INDEX(SORTBY(FILTER(C2:C11,(ED:D11<=XLOOKUP(C2:C11,I2:I9,J2:J9,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),FILTER(J2:J11-D2:D11,(D2:D11>=XLOOKUP(C2:C11,I2:I11,J2:J11,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),-1),1)

The result will need to be filtered by type and material and when the units are greater than zero without the user of a helper column .

If you require any further information just let me know.

Kind Regards
Barney

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	material	product	units			type	material	product	stock
2	metal	product1	2			nut	metal	product4	4
3	metal	product2	2			nut	metal	product5	6
4	metal	product3	4			bolt	metal	product6	0
5	plastic	product4	5			nut	plastic	product7	4
6	metal	product5	8			bolt	metal	product8	6
7	plastic	product6	9			bolt	plastic	product9	6
8	metal	product7	10			bolt	metal	product1	4		nut
9	plastic	product8	0			bolt	plastic	product2	3		metal
10	metal	product9	1			nut	metal	product3	0
11	plastic	product10	6			nut	plastic	product11	6
12											Top Variance 1
13											Top Variance 2
Sheet5 (3)

 

[barney_t]

Apologies, my table was incorrect, please see the below

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	type	material	product	units			type	material	product	stock
2	nut	metal	product1	2			nut	plastic	product4	4
3	nut	metal	product2	2			bolt	metal	product5	6
4	bolt	metal	product3	4			bolt	plastic	product6	0
5	nut	plastic	product4	5			bolt	metal	product7	4
6	bolt	metal	product5	8			bolt	plastic	product8	6
7	bolt	plastic	product6	9			nut	metal	product9	6
8	bolt	metal	product7	10			nut	metal	product1	4		nut
9	bolt	plastic	product8	0			nut	metal	product2	3		metal
10	nut	metal	product9	1			bolt	metal	product3	0
11	nut	plastic	product10	6			nut	plastic	product11	6
12												Top Variance 1
13												Top Variance 2
Sheet5 (3)

 

[Fluff]

In the 1st part of the formula you have D<=J, but in the 2nd its D>=J, they should be the same.

 

[barney_t]

Hi Fluff,
Thank you for highlighting this.

Sorry, it was my error when typing, both should be >=

=INDEX(SORTBY(FILTER(C2:C11,(D:D11>=XLOOKUP(C2:C11,I2:I9,J2:J9,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),FILTER(J2:J11-D2:D11,(D2:D11>=XLOOKUP(C2:C11,I2:I11,J2:J11,0))*(A2:A11=L8)*(B2:B11=L9)*(D2:D11>0),""),-1),1)

The same issue should remain, that is the results are in wrong order.

kind regards

 

[Fluff]

As none of the nut/metal combinations have units greater than stock & the fact you have not shown the result of your formula, it's difficult to know what to suggest.

 

[barney_t]

Hi Fluff

Thank you for your patience.
I have attached an amended table with formulas /results.

Just to recap, I need to find the top 10 products where the units are greater than or equal to the stock with the greatest variance at the top (on the attached I realize there are not 10 matches).

The issue is that although it returns those products that meet the criteria, they are in the wrong order, the greatest negative variance should be at the top result but the results seem to appear randomly. - (I want to keep zero variances but this should be towards the bottom of the list of results - Results greater than zero should not be reported).

To make it easier to see what is going on I have changed the type and material to be all the same - this will not be the case when on the final table. I have also added a simple look up next to the results so you can see what the variance results are for the products returned (this will not be included in the final file). With the data supplied product6 should be at the top of the list and product4 should be the last entry.


many thanks for your help

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	type	material	product	units			type	material	product	stock
2	bolt	metal	product1	2			bolt	metal	product5	5
3	bolt	metal	product2	8			bolt	metal	product6	5
4	bolt	metal	product3	8			bolt	metal	product7	5
5	bolt	metal	product4	5			bolt	metal	product8	7
6	bolt	metal	product5	6			bolt	metal	product9	8
7	bolt	metal	product6	10			bolt	metal	product10	5
8	bolt	metal	product7	6			bolt	metal	product1	-1		bolt
9	bolt	metal	product8	6			bolt	metal	product2	5		metal
10	bolt	metal	product9	1			bolt	metal	product3	5
11	bolt	metal	product10	1			bolt	metal	product4	5			Top Variances	rank	variance check
12													product7	1	-1
13													product6	2	-5
14													product2	3	-3
15													product3	4	-3
16													product4	5	0
17													product5	6	-1
18													product1	7	-3
19														8	NA
20														9	NA
21														10	NA
Sheet1

Cell Formulas
Range	Formula
M12:M21	M12	=IFERROR(INDEX(SORTBY(FILTER($C$2:$C$11,($D$2:$D$11>=XLOOKUP($C$2:$C$11,$I$2:$I$11,$J$2:$J$11,0))*($A$2:$A$11=$L$8)*($B$2:$B$11=$L$9)*($D$2:$D$11>0),""),FILTER($D$2:$D$11-$J$2:$J$11,($D$2:$D$11>=XLOOKUP($C$2:$C$11,$I$2:$I$11,$J$2:$J$11,0))*($A$2:$A$11=$L$8)*($B$2:$B$11=$L$9)*($D$2:$D$11>0),""),-1),N12),"")
O12:O21	O12	=IFERROR(VLOOKUP(M12,I:J,2,0)-VLOOKUP(M12,C:D,2,0),"NA")

 

[Fluff]

Do you have the LET function in your version of xl?

 

[barney_t]

Sorry I don’t

 

[Fluff]

Not a problem, just makes the formula a bit longer. Try

=IFERROR(INDEX(SORTBY(FILTER($C$2:$C$11,($D$2:$D$11>=XLOOKUP($C$2:$C$11,$I$2:$I$11,$J$2:$J$11,0))*($A$2:$A$11=$L$8)*($B$2:$B$11=$L$9)*($D$2:$D$11>0),""),FILTER($D$2:$D$11-XLOOKUP($C$2:$C$11,$I$2:$I$11,$J$2:$J$11,0),($D$2:$D$11>=XLOOKUP($C$2:$C$11,$I$2:$I$11,$J$2:$J$11,0))*($A$2:$A$11=$L$8)*($B$2:$B$11=$L$9)*($D$2:$D$11>0),""),-1),N12),"")