Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: find Unique values (Excel 2003)

    attached is my list which is in column A.
    I merged two lists into one and wanted to advance filter the large list and find any values that are unique and not duplicated

  2. #2
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    find Unique values (Excel 2003)

    I have a problem with a list that is 1 col by 6000 rows
    the problem is there is unique values (30 to be exact) in the list and I want to find them
    Any one know a way to do this

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: find Unique values (Excel 2003)

    A quick and dirty way is to put the list in ascending order and in a new column place:

    =if(A2=A1,"Duplicate","")

    Filldown the column and you can then use an autofilter to list the duplicates
    Jerry

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: find Unique values (Excel 2003)

    My apologies, I am looking for the unique values and not the duplicates. I changed my original post but you are very quick and thank you for that

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find Unique values (Excel 2003)

    If the data is in column A, enter the following formula in cell B1.

    <code>
    =IF(COUNTIF($A$1:$A$6000,A22)>1,"Duplicate","")
    </code>

    Now select cell B1 and double click on the fill handle in the lower right corner. This should fill the formula down as far as the data in column A and give you the word "Duplicate" next to all of the duplicates.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find Unique values (Excel 2003)

    Modify the formula in my other reply to:

    <code>
    =IF(COUNTIF($A$1:$A$6000,A22)>1,"","Unique")
    </code>

    And follow the same instructions.
    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: find Unique values (Excel 2003)

    Thank you

  8. #8
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find Unique values (Excel 2003)

    I don't know if you found your answer but I wanted to point you to another thread, posting # 411,338, that may provide what you seek (if you have not already found it.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: find Unique values (Excel 2003)

    Just for information, you could also do this using the advanced filter. Insert a header row above your data and enter a header in A1 (let's say 'Value'). Then enter a criteria header (let's say 'Unique') in D1 and in D2 enter this formula:
    <code>=COUNTIF($A$2:$A$6130,A2)=1</code>
    Then choose Data->Filter->Advanced Filter form the menu, select $A$1:$A$6130 as the List Range, and select $D$1:$D$2 as the criteria range and click OK.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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