SORT(UNIQUE) function

[thewiseguy]

Hello all,

I am currently using the following formula/function:

=SORT(UNIQUE(FILTER(Proposal_Input!AA3:AA2002,(Proposal_Input!AA3:AA2002 <>"")*(Proposal_Input!AD3:AD2002<>0),FALSE)))

This is giving me the correct output with the exception of the first value it returns. I have noticed this in other areas of my worksheet but those first line items were left blank or with a "0" so it didn't hurt me.

Any ideas why this is doing this?

Any help is greatly appreciated!

 

[awoohaw]

what value are in AA3, and AD3?

 

[Fluff]

This is giving me the correct output with the exception of the first value it returns

What is that value?

Also can you post some sample data.

 

[thewiseguy]

trying to get the Xlbb to work but anytime i try to capture data, it just runs and runs

 

[awoohaw]

then copy and paste into table. You'll have to label the columns with column letters and rows with row numbers for the forum to follow your formulas.
Also post the formula somewhere and what cell it is in, as well as an expected value.

 

[thewiseguy]

AA AB AC AD

MAXLITE 27W LED 2x4 Back-Lit Flat Panel MCCT	MLFP24G427WCSCR	2	2
ENVISIONLED Straight Arm Mount for ARL3 Area light, White	ARL3-SA-WH	2
LEVITON High Bay Occupancy Sensor	OSFHU-ITW	2
MAXLITE 27W LED 2x4 Back-Lit Flat Panel MCCT	MLFP24G427WCSCR	2	2
ENVISIONLED Straight Arm Mount for ARL3 Area light, White	ARL3-SA-WH	2
MAXLITE Network Node Rectangular, White	NN-RTW	2
GOODLITE 9W LED Tube Lamp 41K	G-83407	4	2
GOODLITE 9W LED Tube Lamp 41K	G-83407	4	2

These are the values I am getting as a result of the original formula:

ENVISIONLED Straight Arm Mount for ARL3 Area light, White

GOODLITE 9W LED Tube Lamp 41K

MAXLITE 27W LED 2x4 Back-Lit Flat Panel MCCT

The first value (ENVISIONLED) should not be retruned b/c there is no value in column AD associated with it.

Hoping this is clear and will help?

 

[Fluff]

he first value (ENVISIONLED) should not be retruned b/c there is no value in column AD associated with it.

Check that the cell is totally empty

 

[thewiseguy]

Check that the cell is totally empty

there is a formula in it. does that count as "empty?"

 

[thewiseguy]

there is a formula in it. does that count as "empty?"

i also removed all the formulas and hard-entered the data. the first line item is still showing

 

[thewiseguy]

i should clarify what I am looking for:

all unique values in column AA but only if there is a number in column AD (same row)

it looks like my formula is pulling the value from column AA even if there is no number in AD