UNIQUE FILTER giving me a blank row

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
1,015
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
2Mfg/ItemItemApplicationTypeManufacturer
3MAXLITE 2X4 LED Flat Panel [WS-MCCT]2X4 LED Flat Panel [WS-MCCT]Flat PanelMAXLITE
4MAXLITE 2X2 LED Flat Panel [WS-MCCT]2X2 LED Flat Panel [WS-MCCT]Flat PanelMAXLITE
5 #N/A#N/A
Fixtures (BOM)
Cell Formulas
RangeFormula
A3:A5A3=(UNIQUE(FILTER(Input!W3:W3502,(Input!U3:U3502="fixture"))))
B3:B5B3=IFERROR(RIGHT(A3,LEN(A3)-FIND(" ",A3)),"")
C3:C5C3=(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:D5D3=(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
BCDEFGHIJKLMNOPQRSTUVWXYZAA
3496500 Fixture
3497Accessory
3498Accessory
3499Materials
3500Materials
3501Rental
3502Control
Input
Cell Formulas
RangeFormula
B3496B3496=B3489+1
T3496T3496=IF('Lighting&Controls(PSEGrebates)'!$BH3993<0,"NO","")
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
i'm assuming filter only appears once in U3:U3502
 
Upvote 0
im not understanding. filter does not appear anywhere in column U
 
Upvote 0
any spaces at all
=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))

without a space

Book4
LMNOPQRSTUVW
1a1
2a2
3fixturea1
4
5fixturea2
6
7fixturea2
8
Sheet1
Cell Formulas
RangeFormula
M1:M2M1=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))
Dynamic array formulas.


with a space
Book4
LMNOPQRSTUVW
1a1
2a2
3a2fixturea1
4
5fixturea2
6
7fixturea2
Sheet1
Cell Formulas
RangeFormula
M1:M3M1=UNIQUE(FILTER(W3:W3502,U3:U3502="fixture"))
Dynamic array formulas.
 
Upvote 0
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
BCDEFGHIJKLMNOPQRSTUVWXYZAA
34650Interior131200.13Day ProgramRoom 21Troffer 2x4 F32T8 4L2 FixtureFlat PanelMAXLITE 2X4 LED Flat Panel [WS-MCCT]2
347Accessory
348Accessory
349Materials
350Materials
351Rental
352Control
35351 Fixture
354Accessory
355Accessory
356Materials
357Materials
358Rental
359Control
Input
Cell Formulas
RangeFormula
B346,B353B346=B339+1
T346T346=IF('Lighting&Controls(PSEGrebates)'!$BH393<0,"NO","")
T353T353=IF('Lighting&Controls(PSEGrebates)'!$BH408<0,"NO","")
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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
Back
Top