UNIQUE function returning 0

[thewiseguy]

Hi all. I am using the UNIQUE function on a column full of text and the last unique value being returned is a 0. I have this occurring in 2 different UNIQUE functions.

How can I check the reference cells to see where this is coming from?

Any help is greatly appreciated.

 

[thewiseguy]

You are correct. The cell contains data but restricted by formula:

Proposal5.xlsm
	X	Y
1	Product Used for BOM/Labor	Qty Used for BOM/Labor
2
3	T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 5K	9
INPUT

Cell Formulas
Range	Formula
X2:X3	X2	=IF(N2="Yes","",Q2)
Y2:Y3	Y2	=IF(X2="","",T2)

 

[Fluff]

Those cells should not cause a problem, but without more data & a clear understanding of what you are trying to do, I cannot say more.

 

[thewiseguy]

Those cells should not cause a problem, but without more data & a clear understanding of what you are trying to do, I cannot say more.

Here is the reference data:

Proposal5.xlsm
	Q	R	S	T	U	V	W	X	Y
1	Fixture/Lamp	Auxiliary Product	Materials	Fixture/Lamp Qty.	Aux. Product Qty.	Materials Qty.	Location Notes	Product Used for BOM/Labor	Qty Used for BOM/Labor
2	T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 5K	Constant Wattage LED Emergency Back-Up Kit	1/2 Inch Locknut	22		5
INPUT

Cell Formulas
Range	Formula
T2	T2	=IF(OR(P2="Tubes2Ft",OR(P2="Tubes3Ft",OR(74="Tubes4Ft",OR(74="Tubes8Ft",OR(74="TubesUBend",OR(74="Lamps,")))))),(M2*VLOOKUP(J2,ExistingFull,5,0)),M2)
X2	X2	=IF(N2="Yes","",Q2)
Y2	Y2	=IF(X2="","",T2)

Here is the output data, returning '0'

Proposal5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Manufacturer	Item	FixtureCategory	Product ID	Qty	Cost	Line	PO	QtyOrdered	Shipper	Tracking	Received	On-Site
2		0
3	ASD	ASD 10W LED Security Light 5K	Security	ASD-LSR-1050-AP	1	$32.50	$32.50
BOM_FixLamps

Cell Formulas
Range	Formula
B2:B19	B2	=SORT(UNIQUE(FILTER(INPUT!X2:X200,INPUT!X2:X200 <>"",FALSE)))
C2:C3	C2	=IFERROR(VLOOKUP(B2,BOMProductNoRange,2,0),"")
D2:D3	D2	=IFERROR(VLOOKUP(B2,INDIRECT(VLOOKUP(C2,TableNames,2)),8,0),"")
E2:E3	E2	=IFERROR(IF(SUMPRODUCT(--(INPUT!$X$2:$X$200=B2),INPUT!$Y$2:$Y$200)=0,"",SUMPRODUCT(--(INPUT!$X$2:$X$200=B2),INPUT!$Y$2:$Y$200)),"")
F2:F3	F2	=IFERROR(VLOOKUP(B2,INDIRECT(VLOOKUP(C2,TableNames,2)),17+MATCH(E2,{0,21,51,101},1),0),"")
G2:G3	G2	=IFERROR((E2*F2),"")
A2:A3	A2	=IFERROR(LEFT(B2,FIND(" ",B2)-1),"")
Dynamic array formulas.

 

[Fluff]

Not sure why you are getting the 0, but then with only one row of data, that's not surprising.
Best guess is that somewhere in X2:X200, you either have a 0, or a cell that is not totally empty, or contains a ""
With that formula empty cells & cells returning "" will be ignored.

 

[thewiseguy]

Not sure why you are getting the 0, but then with only one row of data, that's not surprising.
Best guess is that somewhere in X2:X200, you either have a 0, or a cell that is not totally empty, or contains a ""
With that formula empty cells & cells returning "" will be ignored.

what is the definition of "not totally empty?

 

[Fluff]

The cell contains something.

 

[thewiseguy]

The cell contains something.

so each cell contains the same formula, just copied down. could it be something in the reference cells?

column 'N' and 'O' are both validations. Or could it be formatting?

 

[Fluff]

could it be something in the reference cells?

I have no idea, because I can't see them.
Do you have the Input sheet setup to hide cells with 0

 

[thewiseguy]

I have no idea, because I can't see them.
Do you have the Input sheet setup to hide cells with 0

ahh i did make a few adjustments to the custom formatting. but that should only affect it in column X or Q, correct?

 

[Fluff]

Depends on what columns you changed the formatting on.