Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select distinct entries from a list (2000 SR 1)

    I have a column with 1000 rows and I want a list of distinct values. Any ideas of the best way to do this?

    For example

    the list:
    fred
    john
    fred
    james
    sue
    sue
    jane

    distinct list:
    fred
    john
    james
    sue
    jane

    I am doing this too often to import into access and use Select Distinct query.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select distinct entries from a list (2000 SR 1)

    Here's a link to Chip Pearsons Site decribing how to do this:

    Extracting Unique Entries

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

    Re: Select distinct entries from a list (2000 SR 1)

    The code in This Post will delete all rows that have duplicates in column A. Is that what you want to do, or do you want some kind of separate list.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select distinct entries from a list (2000 SR 1)

    Make a pivot table. Use "the list:" as the row source and "Count of the list:" as the data field. Leave the column source blank.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select distinct entries from a list (2000 SR 1)

    Thanks for the reponse guys, the link to www.cpearson.com/excel/duplicat.htm has got me close enough.

    I wonder can anyone combine the "extracting unique entries" and "eliminating blank cells" formula on these pages into a function that would do exactly what I am after.

    thanks

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select distinct entries from a list (2000 SR 1)

    If you really mean that you want a function, then your answer is on John Walkenbach's tips. You would just need an IF statement in the code to skip over blank cells.

    You can do this manually with the Data | Advanced Filter | Unique Records only. Microsoft has an example. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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