365 Array Formulas - SORT & FILTER from 2 different columns??

[thewiseguy]

Hi All,

Is it possible to combine these 2 SORT & FILTER formulas?? I am trying to have my output data on 1 sheet, coming from sorting and filtering from 2 different columns of data.

20201117-Bob.2020.IntNFRtr.v3 - WIP.xlsx
	A	B	C
1	Category	Manufacturer	Item
2	Flat Panel	ASD	ASD 22W LED Flat Panel 2x2 MV 4K Recessed
BOM_FixLamps

Cell Formulas
Range	Formula
A2	A2	=IFERROR(VLOOKUP(C2,TableProposed,3,0),"")
B2	B2	=IFERROR(LEFT(C2,FIND(" ",C2)-1),"")
C2:C23	C2	=SORT(UNIQUE(FILTER(Input!Z4:Z503,(Input!Z4:Z503 <>"")*(Input!Z4:Z503<>0),FALSE)))
Dynamic array formulas.

20201117-Bob.2020.IntNFRtr.v3 - WIP.xlsx
	T	U	V
1	Category	Manufacturer	Item
2	Rental Equip	Ahern	Ahern W LED Rental Equip Scissor lift, 19', 32" Electric - Week
BOM_FixLamps

Cell Formulas
Range	Formula
T2	T2	=IFERROR(VLOOKUP(V2,TableProposed,3,0),"")
U2	U2	=IFERROR(LEFT(V2,FIND(" ",V2)-1),"")
V2:V3	V2	=SORT(UNIQUE(FILTER(Input!W4:W503,(Input!W4:W503 <>"")*(Input!W4:W503<>0),FALSE)))
Dynamic array formulas.

Any help is appreciated!!

 

[jasonb75]

Not in a single step, I've been trying many ways to do something similar. With the current formulas the 2 columns would be treated as relative rather than continuous, meaning that Z5 would be placed next to W5, etc.

The only way to make it work would be to combine the 2 lists in simple format first, e.g. by entering =Z4:Z503 into W504, then processing the whole lot as a single column using your existing method.

It might be possible with PQ, that is not something that I use enough to say for certain.

 

[thewiseguy]

Not in a single step, I've been trying many ways to do something similar. With the current formulas the 2 columns would be treated as relative rather than continuous, meaning that Z5 would be placed next to W5, etc.

The only way to make it work would be to combine the 2 lists in simple format first, e.g. by entering =Z4:Z503 into W504, then processing the whole lot as a single column using your existing method.

It might be possible with PQ, that is not something that I use enough to say for certain.

thanks for the feedback. looks like i'll stick with what I have for now.

 

[jasonb75]

Just a quick follow up, something I did notice was that an 'Array of ranges' is one of the causes of the #CALC! error.
Whilst I did not see this specific error in all methods that I tried, the need to use an array of ranges appears unavoidable so it seems unlikely that it will be possible to work around it.

How to correct a #CALC! error - Microsoft Support

Learn more about #CALC! errors in Excel dynamic array functions.

support.microsoft.com