Extracting unique values from a column without zeros or n/a

[snoproladd]

So after some searching I found most of what I needed to extract the value(s) I need out of an array in my workbook. I found this link very helpful and comprehensive...



http://www.get-digital-help.com/2009...om-one-column/


In the document it describes the basic array formula I need, as well as 2 others to handle blanks and N/A's.

Here are the 3:

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))
This is the basic string, entered as an array formula

=IF(ISNA(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0))), "", INDEX(List, MATCH(0, COUNTIF($B$1:B1, List), 0)))
This eliminates the N/A's in Excel 2003 which I am using

=INDEX(List,MATCH(0,IF(ISBLANK(List),"",COUNTIF($B$1:B1,List)),0)) + CTRL + SHIFT + ENTER
This handles blank cells in my named range, which there will always be

=INDEX(List,MATCH(0,(List="")+COUNTIF($B$1:B1,List)),0)) + CTRL + SHIFT + ENTER
This is another version of the same basic formula another user submitted

I am having trouble, however, nesting the 'blanks' formula and the 'n/a' formula together. All formulas work well on their own, but not together. Any suggestions?

[zbor]

Can you upload example workbook?

[snoproladd]

I will try...the last time I tried to upload I failed lol

[zbor]

Must be less than 1MB

[snoproladd]

ReworkInventory Withdrawal Stripped.xls

The forumula I'm entering is on Totals!B17. It references the named range Rework_Records on the first sheet. In the formula, this ranges is as list, and B$1$:B1 should be $b16$:b16 in my formula.

[zbor]

So you just want to remove those #N/A?
Didn't test functionality...

[snoproladd]

Thank you so much, it works fine. If you have 5 minutes in the next few days, could you roughly explain what you did? I've never used most of those funtions before.

[snoproladd]

SOLVED!I just can't figure out where to note that so it gets recorded.

[zbor]

It's just a trick that you can implify in any formula that return text or error as result.
A previous formula you had in cell I'll just continue to call your_formula.

A LOOKUP formula will return you LAST value in an array ignoring errors.
So, LOOKUP(REPT("Z",255), A1:A10) will return last value of a array that is not error.

=LOOKUP("z",{"a","d","c","b"})
will retun b because it can never find z so it look to the end of the range.

To make sure that it will never find text string we will put very long ZZZZZZZZZZZZZZZZZZZZZZZ, actually Z repeated 255 times (REPT("Z",255)


CHOOSE allows you to build an array of 2 values against which you can search according to the criteria value (in our case Z repeated 255 times)

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",your_formula))

Basicaly, it will search last value of "" and your_formula. If your_formula return error it will return previous last value (that is "" aka blank)


=LOOKUP(REPT("Z",255),CHOOSE({1,2},"","snoproladd"))

will return snoproladd

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",NA())

will return Blank as #N/A is ignored given it is not text like the criteria.

[snoproladd]

That actually makes sense. Thanks, I'll try to implement on my own in the future. As a question, you'll notice I have 800 lookup combo boxes on the first page. Can I change the 255 to 400 or so to be sure if I fill that entire page with records I'll never have issues?

One of these days I'll go to Access with this and make it even better, but that's a whole nother story.

[zbor]

If you asking about REPT("Z",255) then answer is no. You don't need to change 255 because it doesn't have anything with data. It's a long string that excel compare with other. Z is because ZZZZ...ZZ is bigger than AAAA...AA so basicaly that's a value that can be never reach. You could use less (as I showed in example with single "z"). But why to risk? Use this as a ultimate reference and you'll never have problem.

[snoproladd]

Oh, Ok. Its sinking in, but slowly.