Index sortby Filter

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKL
1materialproductunitstypematerialproductstock
2metalproduct12nutmetalproduct44
3metalproduct22nutmetalproduct56
4metalproduct34boltmetalproduct60
5plasticproduct45nutplasticproduct74
6metalproduct58boltmetalproduct86
7plasticproduct69boltplasticproduct96
8metalproduct710boltmetalproduct14nut
9plasticproduct80boltplasticproduct23metal
10metalproduct91nutmetalproduct30
11plasticproduct106nutplasticproduct116
12Top Variance 1
13Top Variance 2
Sheet5 (3)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Apologies, my table was incorrect, please see the below

Book1.xlsx
ABCDEFGHIJKL
1typematerialproductunitstypematerialproductstock
2nutmetalproduct12nutplasticproduct44
3nutmetalproduct22boltmetalproduct56
4boltmetalproduct34boltplasticproduct60
5nutplasticproduct45boltmetalproduct74
6boltmetalproduct58boltplasticproduct86
7boltplasticproduct69nutmetalproduct96
8boltmetalproduct710nutmetalproduct14nut
9boltplasticproduct80nutmetalproduct23metal
10nutmetalproduct91boltmetalproduct30
11nutplasticproduct106nutplasticproduct116
12Top Variance 1
13Top Variance 2
Sheet5 (3)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,691
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,691
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1typematerialproductunitstypematerialproductstock
2boltmetalproduct12boltmetalproduct55
3boltmetalproduct28boltmetalproduct65
4boltmetalproduct38boltmetalproduct75
5boltmetalproduct45boltmetalproduct87
6boltmetalproduct56boltmetalproduct98
7boltmetalproduct610boltmetalproduct105
8boltmetalproduct76boltmetalproduct1-1bolt
9boltmetalproduct86boltmetalproduct25metal
10boltmetalproduct91boltmetalproduct35
11boltmetalproduct101boltmetalproduct45Top Variancesrankvariance check
12product71-1
13product62-5
14product23-3
15product34-3
16product450
17product56-1
18product17-3
19 8NA
20 9NA
21 10NA
Sheet1
Cell Formulas
RangeFormula
M12:M21M12=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:O21O12=IFERROR(VLOOKUP(M12,I:J,2,0)-VLOOKUP(M12,C:D,2,0),"NA")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,691
Office Version
  1. 365
Platform
  1. Windows
Do you have the LET function in your version of xl?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,691
Office Version
  1. 365
Platform
  1. Windows
Not a problem, just makes the formula a bit longer. Try
Excel Formula:
=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),"")
 
Solution

Forum statistics

Threads
1,176,025
Messages
5,900,962
Members
434,861
Latest member
Coffee_in_a_jiffy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top