Advanced Filter Unique Records Only - treats dependent cells inconsistently

[Bill Benson]

My most useful macro extracts distinct items from a range to a new sheet.

Native Excel would handle this I think by using Advanced Filter

no Criteria range
Unique records only box checked
Goto Special - visible cells only
Copy range, paste elsewhere

I see a peculiarity which gives me pause for thought.

Case I
A1 = "a"
A2 = "=A1"
A3 = "=A2"
Result: Only the first item is treated as Unique, the rest are hidden.
Great.

Case II
A1 = "=A2"
A2 = "=A3"
A3 = "a"
Result: All items are treated as Unique, none are hidden. Not so great (in
my opinion)

Apparently when a cell's value depends on a row above that cell, it is
treated as non-unique and filtered, but the reverse is not considered?

A bug? an explainable mystery? Is there no workaround except pasting the
cells as values somewhere else before applying the technique?

Thanks.

 

[Shane Devenshire]

Hi,

1. Works on my machine either way (what version of Excel are you using?)
2. You should have a title at the top of the data
3. When you want to copy the results of an autofilter you don't need to use
Paste Special, Visible cells only, unless you are using a very old version of
Excel, just copy and paste. Of course this has nothing to do with Advanced
Filter

 

[Bill Benson]

hi Shane, I misled with the cells and there was a title

A1 = "Title"
A2 = "a"
A3 = "=A4"
A4 = "=A5"
A5 = "=A6"
A6 = "=A7"
A7 = "b"

I am using Excel 2003
I am using Advanced Filter not Autofilter so I am not sure what you meant by
the last comment.

And when I use Advanced Filter Unique records only, I get

Title
a
a
a
a
a
b

but when I have this combination

A1 = "Title"
A2 = "a"
A3 = "=A2"
A4 = "=A3"
A5 = "=A4"
A6 = "=A5"
A7 = "=A6"

then I get

Title
a
b

Thanks.