Remove blanks dynamically in range with blank cells

[gjrr4x1]

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

[Richard Buttrey]

Hi,

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

[gjrr4x1]

Hi Richard,

Sorry for my poorly written thread.

Please find attached a workbook with what I'm looking for.

I would like to obtain a list of all the different countries there's in the range B3:B30.


Thank you for your help.

[Richard Buttrey]

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'.

[twiggywales]

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"

Please Login or Register  to view this content.

then the formula to drag for a list will be this

Please Login or Register  to view this content.

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)