Remove blanks dynamically in range with blank cells

Edit: Remove duplicates!

I would like to remove duplicates and blanks from a range of cells dynamically.

In this fantastic video I can see how to do it from a list, but when there's blanks between the cell range it doesn't work.

http://www.youtube.com/watch?v=IZLAzIYfMDU


E.g. Range A1:A40 and there's blanks in A10, A25, A27, A35.


Thank you in advance.

Best regards,

Guillermo

Hi,

Rather than asking us to watch videos why don't you upload YOUR workbook and tell us what YOU want?

Hi,

Here's one way that uses a dynamic range name "lst" that expands and contracts automatically to cover your range of names, a pivot table based on that range, and a sheet change macro that automatically refreshes the pivot table when you add a new country.

There are no doubt array formulas too that you could use although these tend to be a bit opaque and difficult to 'read'.

if there is a blank at the start this will cause problems (so ill start your range at B4)

in named ranges (ctrl+F3) enter this and call it "Range"

=Hoja1!$B$4:INDEX(Hoja1!$B$4:$B$1000,MAX(INDEX(Hoja1!$B$4:$B$1000<>"",,)*ROW(Hoja1!$B$4:$B$1000)))

then the formula to drag for a list will be this

=IFERROR(INDEX(Range,SMALL(IF(FREQUENCY((IFERROR(CODE(TRIM(Range)),0)>64)*(IFERROR(MATCH(Range,Range,0),0)),ROW(Range)-MIN(ROW(Range))+1)>0,ROW(Range)-MIN(ROW(Range))+1,""),ROW($A13))),"")

entered with Crtl+Shift+Enter

see attached sheet as to how it works.

Note there may be simpler ways but i worked this out before and it defiantly works

Book 1 Remove Dups.xlsx (your file).

Remove Duplicates Breakdown and Max with Index.xlsx (my explanation file)