+ Reply to Thread
Results 1 to 7 of 7

Identifying Unique ID's in a Single Column

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Identifying Unique ID's in a Single Column

    Hello -
    I have thousands of ID's in a single column and need to find all ID's that have a (-#) the # are varied; 1, 2 3 etc... at the end. Sorting does not work, the -# are at the end of the ID.
    Thank You!!

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Identifying Unique ID's in a Single Column

    Try the autofilter - Custom - Select "ends with" and use -* as string ( that is supposing ther are no other such strings in the cell - If not please provide a small sample sheet

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Identifying Unique ID's in a Single Column

    In a helpcolumn =right(a1,1) and then you can filter on that.

    Otherwise post an excelfile without confidentional information.

    Please also add the desired (expected) result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Identifying Unique ID's in a Single Column

    Here is an example. The raw data is in column A

    The formula in column B displays only those values with the trailing -*.
    Column C results from copying column B over and sorting descending.
    Applying an Advanced filter to column C and selecting unique records only results in column D
    Attached Files Attached Files
    Gary's Student

  5. #5
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Identifying Unique ID's in a Single Column

    Pepe - Thank you for the suggestion, I am dealing with 10's of thousand of rows, to many for the auto filter. My apologies for not sharing that in my original post.

  6. #6
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Identifying Unique ID's in a Single Column

    oeldere - Thanks for the suggestion. That did not work, it provided me with the final digit in the ID. I need to find only the ID's with a -1 or -2 or -3 etc... I am dealing with 10's of thousands of ID's so filtering does not work either.

  7. #7
    Registered User
    Join Date
    04-24-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Identifying Unique ID's in a Single Column

    Jakobshavn - Thank You, this worked great!! Exactley what I need...Thank You!!

+ 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