Results 1 to 5 of 5

Thread: Distinct Text

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Distinct Text

    Apparently, MS added a new function in 2013 called DISTINCT that returns a 1-column list that contains distinct values from a specific column.
    For those of us running 2010 or 2007, we don't have that one.

    I'm wondering if someone wrote a UDF that does this for 2010?

    I don't want to have to manually filter the data.

    Based on a set of criteria from a sheet (e.g., based on the last name in a column on one sheet, the products this person ordered from the company are listed in another column, on the same sheet -- and, I'd like to be able to list the unique products based on the last name criteria on another sheet).

    I suspect this is a macro of some kind or UDF. Anyone have one?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It can be done with Advanced filter and has been available since XL97.

    A macro would have to obtain the same information that the Adv filter already asks and it is already created, I don't see the benefit of creating an alternate version to an existing procedure.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In addition to adv filter, a pivot table using the column and the count of column will give a unique list which can be copied and pasted to a new location and the pivot table deleted...

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    BTW, the DISTINCT function in XL2013 from I just read, does not do what you describe it doing. It does not generate a physical list (which can be put into the spreadsheet), it generates a list which can be used in calculations (see the description at http://office.microsoft.com/en-us/ex...102838209.aspx)

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I know how to do it with an advanced filter, but a user doesn't.

    Interestingly, I found an interesting VBA function by CPearson that I can adapt and solve my problem.

Posting Permissions

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