Extracting unique values from a column without zeros or n/a
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?
Can you upload example workbook?
"Relax. What is mind? No matter. What is matter? Never mind!"
I will try...the last time I tried to upload I failed lol
Must be less than 1MB
"Relax. What is mind? No matter. What is matter? Never mind!"
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.
So you just want to remove those #N/A?
Didn't test functionality...
"Relax. What is mind? No matter. What is matter? Never mind!"
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.
SOLVED!I just can't figure out where to note that so it gets recorded.
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.
Last edited by zbor; 02-13-2012 at 10:32 AM.
"Relax. What is mind? No matter. What is matter? Never mind!"
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.
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.
Last edited by zbor; 02-14-2012 at 03:52 AM.
"Relax. What is mind? No matter. What is matter? Never mind!"
Oh, Ok. Its sinking in, but slowly.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks