UNIQUE FILTER giving me a blank row

[thewiseguy]

Hello all -

I can not seem to figure out what is wrong in my souce data, that is giving my UNIQUE-FILTER formula an extra row:

v2023.9.xlsm
	A	B	C	D
2	Mfg/Item	Item	ApplicationType	Manufacturer
3	MAXLITE 2X4 LED Flat Panel [WS-MCCT]	2X4 LED Flat Panel [WS-MCCT]	Flat Panel	MAXLITE
4	MAXLITE 2X2 LED Flat Panel [WS-MCCT]	2X2 LED Flat Panel [WS-MCCT]	Flat Panel	MAXLITE
5			#N/A	#N/A
Fixtures (BOM)

Cell Formulas
Range	Formula
A3:A5	A3	=(UNIQUE(FILTER(Input!W3:W3502,(Input!U3:U3502="fixture"))))
B3:B5	B3	=IFERROR(RIGHT(A3,LEN(A3)-FIND(" ",A3)),"")
C3:C5	C3	=(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Product_Subgroup],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Product_Subgroup],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Product_Subgroup],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Product_Subgroup],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Product_Subgroup],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Product_Subgroup],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Product_Subgroup],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Product_Subgroup],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Product_Subgroup],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Product_Subgroup],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Product_Subgroup],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Product_Subgroup],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Product_Subgroup],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Product_Subgroup],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Product_Subgroup],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Product_Subgroup],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Product_Subgroup],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Product_Subgroup],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Product_Type],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Product_Type]))))))))))))))))))))))))))))))
D3:D5	D3	=(XLOOKUP(A3,Tbl_Area[MaskedPartNumber],Tbl_Area[Brand],XLOOKUP(A3,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[Brand],XLOOKUP(A3,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[Brand],XLOOKUP(A3,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[Brand],XLOOKUP(A3,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[Brand],XLOOKUP(A3,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[Brand],XLOOKUP(A3,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[Brand],XLOOKUP(A3,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[Brand],XLOOKUP(A3,Tbl_Linear[MaskedPartNumber],Tbl_Linear[Brand],XLOOKUP(A3,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[Brand],XLOOKUP(A3,Tbl_Strip[MaskedPartNumber],Tbl_Strip[Brand],XLOOKUP(A3,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[Brand],XLOOKUP(A3,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[Brand],XLOOKUP(A3,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[Brand],XLOOKUP(A3,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[Brand],XLOOKUP(A3,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[Brand],XLOOKUP(A3,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[Brand],XLOOKUP(A3,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[Brand],XLOOKUP(A3,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[Brand],XLOOKUP(A3,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[Brand],XLOOKUP(A3,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[Brand],XLOOKUP(A3,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[Brand],XLOOKUP(A3,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[Brand],XLOOKUP(A3,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[Brand],XLOOKUP(A3,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[Brand],XLOOKUP(A3,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Brand],XLOOKUP(A3,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Brand],XLOOKUP(A3,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Brand],XLOOKUP(A3,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Brand]))))))))))))))))))))))))))))))
Dynamic array formulas.

source data

v2023.9.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
3496	500																			Fixture
3497																				Accessory
3498																				Accessory
3499																				Materials
3500																				Materials
3501																				Rental
3502																				Control
Input

Cell Formulas
Range	Formula
B3496	B3496	=B3489+1
T3496	T3496	=IF('Lighting&Controls(PSEGrebates)'!$BH3993<0,"NO","")

 

[etaf]

i'm assuming filter only appears once in U3:U3502

 

[thewiseguy]

im not understanding. filter does not appear anywhere in column U

 

[etaf]

any spaces at all
=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))

without a space

Book4
	L	M	N	O	P	Q	R	S	T	U	V	W
1		a1
2		a2
3										fixture		a1
4
5										fixture		a2
6
7										fixture		a2
8
Sheet1

Cell Formulas
Range	Formula
M1:M2	M1	=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))
Dynamic array formulas.

with a space

Book4
	L	M	N	O	P	Q	R	S	T	U	V	W
1		a1
2		a2
3		a2								fixture		a1
4
5										fixture		a2
6
7										fixture		a2
Sheet1

Cell Formulas
Range	Formula
M1:M3	M1	=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))
Dynamic array formulas.

 

[thewiseguy]

so i was able to pinpit where the problem is coming from by adjust the range. its been rows 350 and 360. coincidentally, this is where my data stops. what is this telling me?

v2023.9.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
346	50	Interior	1	3120	0.13		Day Program	Room 21				Troffer 2x4 F32T8 4L	2							Fixture	Flat Panel	MAXLITE 2X4 LED Flat Panel [WS-MCCT]	2
347																				Accessory
348																				Accessory
349																				Materials
350																				Materials
351																				Rental
352																				Control
353	51																			Fixture
354																				Accessory
355																				Accessory
356																				Materials
357																				Materials
358																				Rental
359																				Control
Input

Cell Formulas
Range	Formula
B346,B353	B346	=B339+1
T346	T346	=IF('Lighting&Controls(PSEGrebates)'!$BH393<0,"NO","")
T353	T353	=IF('Lighting&Controls(PSEGrebates)'!$BH408<0,"NO","")

 

[thewiseguy]

So column U is fixed. These values will always be there. Many times there will be no value in column W, so how would I write the UNIQUE FILTER formula so that it does not return the 0?