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

**Magsy** · 12-13-2011, 06:18 AM

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

**Fotis1991** · 12-13-2011, 06:46 AM

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.

**Magsy** · 12-13-2011, 07:39 AM

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