UNIQUE function returning 0

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
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

Well-known Member
Joined
May 23, 2005
Messages
773
Office Version
365
Platform
Windows
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
QRSTUVWXY
1Fixture/LampAuxiliary ProductMaterialsFixture/Lamp Qty.Aux. Product Qty.Materials Qty.Location NotesProduct Used for BOM/LaborQty Used for BOM/Labor
2T-1 Lighting 135W LED Linear High Bay w/ Occ. Sensor 5KConstant Wattage LED Emergency Back-Up Kit1/2 Inch Locknut225  
INPUT
Cell Formulas
RangeFormula
T2T2=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)
X2X2=IF(N2="Yes","",Q2)
Y2Y2=IF(X2="","",T2)



Here is the output data, returning '0'

Proposal5.xlsm
ABCDEFGHIJKLM
1ManufacturerItemFixtureCategoryProduct IDQtyCostLinePOQtyOrderedShipperTrackingReceivedOn-Site
2 0     
3ASDASD 10W LED Security Light 5KSecurityASD-LSR-1050-AP1$32.50$32.50
BOM_FixLamps
Cell Formulas
RangeFormula
B2:B19B2=SORT(UNIQUE(FILTER(INPUT!X2:X200,INPUT!X2:X200 <>"",FALSE)))
C2:C3C2=IFERROR(VLOOKUP(B2,BOMProductNoRange,2,0),"")
D2:D3D2=IFERROR(VLOOKUP(B2,INDIRECT(VLOOKUP(C2,TableNames,2)),8,0),"")
E2:E3E2=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:F3F2=IFERROR(VLOOKUP(B2,INDIRECT(VLOOKUP(C2,TableNames,2)),17+MATCH(E2,{0,21,51,101},1),0),"")
G2:G3G2=IFERROR((E2*F2),"")
A2:A3A2=IFERROR(LEFT(B2,FIND(" ",B2)-1),"")
Dynamic array formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
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

Well-known Member
Joined
May 23, 2005
Messages
773
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
The cell contains something.
 

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
773
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
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

Well-known Member
Joined
May 23, 2005
Messages
773
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Depends on what columns you changed the formatting on. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,353
Messages
5,486,367
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top