Retrieve unique values from a list.... while removing blanks.

1. Retrieve unique values from a list.... while removing blanks.

Hi,

Iīm having a lot of trouble trying extract unique values and remove blanks from a list. I need the lists to update automatically so I canīt use autoformat.

Iīve used the following formula, which works great up to row 13. Simply changing the 13, to say 150, doesnīt work... Anyone have any idea?

{=OFFSET(\$A\$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(\$A\$1:\$A\$13)=B\$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

(Source: http://www.exceltip.com/st/Retrievin..._List/805.html)

Any help is much appreciated!

Thanks
Magsy

2. Re: Retrieve unique values from a list.... while removing blanks.

Hi Magsy

Be sure that the best way to describe your problem, is to attach a sample workbook.

Also,I don't understand, why you send this:

(Source: http://www.exceltip.com/st/Retrievin..._List/805.html)

But if you try to work with this formula:

{=INDEX(\$A\$2:\$A\$8,MATCH(0,COUNTIF(\$B\$2:B2,\$A\$2:\$A\$8),0))}

It's works fine(Array formula(CTRL+Shift+ENTER

Hope to helps you.

3. Re: Retrieve unique values from a list.... while removing blanks.

I figured out what was wrong when I was making up a sample workbook....

Basically, the formula above works great if using it once off. Problems arise when it has reaches an NA. Therefore, unfortunately, it canīt filter dynamically changing columns.

Regards
Magsy

4. Re: Retrieve unique values from a list.... while removing blanks.

Problems arise when it has reaches an NA. Therefore, unfortunately, it canīt filter dynamically changing columns.
Trere are some ways to hide the #ΝΑ, appearance...

But without samplebook, we can do nothing..

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1