Advanced filter to get unique values

Hi all,
Referring to the attachment below, I am trying to get the unique numbers in the B column. Most of the numbers in that B column appear more than once, and I would like to get a different column to display only the unique records. So wanted to use the Excel built-in method of filtering (Data->Filter->Advanced Filter) and (after having selected all the numbers in the B column, excluding the "Internal_PN" column header), I have used the options "Copy to another location" and "Unique records only" in order to obtain (on column E) the needed result.
header.JPG
It works fine except for the first value in the column. I get the 101026 number twice. All other numbers in the original column appear just once on column E, except for this very first number which insists on appearing twice. I am using excel 2003, but I noticed the same in excel2007, so it must be something I am doing wrong.

Any help will be greatly appreciated.

Best Regards,
Cristian

Hi Cristian

You can do this, using a formula.

=IF(MAX($e$1:$e1)=MAX($b:$b);"";SMALL($b:$b;1+COUNTIF($b:$b;"<="&N($e1))))

Hope to helps you.

Hi Christian

Welcome to the Forum!

The Advanced filter requires a header. Since you're starting on row 2, it treats row 2 as the header row...therefore you get it twice. Your best bet, if you don't want the header row is to include it in your filter range then delete it.

Hi Christian

You are welcome. Glad to helped you.

So pls, if you found your solution, mark your Thread, as SOLVED.

If you use VBA (and this is the programming subforum) you can place the result whereever you want.

As Jaslake pointed out: in Excel filtering assumes a columnheader: so the first item in a column will always be copied to another location. Below the copied columnheader the unique items appear.

To filter column B in sheet1 for unique items to sheet2 column K:

Please Login or Register  to view this content.

Hi brumbarchris

Wrong on both points...see attached.