+ Reply to Thread
Results 1 to 8 of 8

Advanced filter to get unique values

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Timisoara, Romania
    MS-Off Ver
    Excel 2003
    Posts
    2

    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
    Attached Files Attached Files
    Last edited by brumbarchris; 01-13-2012 at 08:14 AM. Reason: solution found

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Advanced filter to get unique values

    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.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    01-12-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Advanced filter to get unique values

    I am not sure about why your not including the header cell. But including the header cell in the list range field works like a charm.....

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Advanced filter to get unique values

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

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    01-12-2012
    Location
    Timisoara, Romania
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Advanced filter to get unique values

    Hi all thanks for the help.
    Unfortunately, the advanced filter option with uniqie records results in the same repetition of the first value, even if I include the "Internal_PN" header row in the selection and even if I delete it. It just does not work...
    However, the solution provided by Fortis is just great! Particularly because it would allow me to obtain the list of unique records in a different sheet, which the Advanced Filter option does not allow.

    Thanks!

    I still don't understand why the Advanced Filter does not work as expected...

    Regards,
    Cristian

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Advanced filter to get unique values

    Hi Christian

    You are welcome. Glad to helped you.

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

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Advanced filter to get unique values

    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.



  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Advanced filter to get unique values

    Hi brumbarchris

    Wrong on both points...see attached.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1