+ Reply to Thread
Results 1 to 7 of 7

Remove duplicates from single cell

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Remove duplicates from single cell

    Hello,
    In a SINGLE cell (B2 for example) I have a formula (not text) that gives me
    Peter, Paul, George, Paul, Peter
    I am looking for a way to return to another SINGLE cell
    Peter, Paul, George
    In other words, eliminate doubles

    Please note that I may have to do it several times for multiple cells so a formula (if possible) would be preferable. If a formula is not possible a macro would do
    Also the number of names in B2 may vary from time to time

    If it is any help, one idea I have is to copy B2 / paste special values to another cell, use Text To Columns, then copy/paste special transpose, use Remove duplicates and then concatenate them. However, i still have to do it many times and more importantly I am looking for sth more elegant so that I do not need to have an idle range

    Thank you

  2. #2
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Remove duplicates from single cell

    Petros, here is a modification of something similar I had to do. It uses a dictionary object to hold the unique keys which are then concatenated back into the result. Once you create it, you can use it like any other function. I don't really have a lot of time right now, so there is no error handling.

    Please Login or Register  to view this content.
    Greg
    Just a guy trying to make work stuff easier.

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Remove duplicates from single cell

    Hi Greg,
    The dic will handle differently if the words are case sensitive.
    You might need to add
    option compare to your dic.
    Second thought- as it is a function, the case might not matter.

  4. #4
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Remove duplicates from single cell

    Quote Originally Posted by AB33 View Post
    Hi Greg,
    The dic will handle differently if the words are case sensitive.
    You might need to add
    option compare to your dic.
    Second thought- as it is a function, the case might not matter.
    Ah, I had not thought of case sensitivity. Thank you for pointing that out.

  5. #5
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Re: Remove duplicates from single cell

    Thank you gsnidow. BTW far too advanced for me. A lot of new things to understand
    Last edited by Petros Georgilas; 12-19-2014 at 09:06 AM.

  6. #6
    Forum Contributor gsnidow's Avatar
    Join Date
    07-22-2010
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    150

    Re: Remove duplicates from single cell

    Petros, not sure if you mean the code itself is too advanced, or if you do not know what to do with it. I'll assume the latter. Open your workbook, and press the 'alt' and 'f11' keys at the same time. This will open up the Visual Basic Editor. You may or may not see the project explorer on the left of the screen, and if you don't, click View>Project Explorer. Once the project explorer is visible, you should see something that says 'VBAProject (YourWorkbookName)'. Right click it, and choose insert>module. A module is just a container for code. When you do this, the right hand window should open to the module you just inserted. Copy and paste the code I provided in the module. Once the code is there, you can use the function in a worksheet cell. For example, if your list of names is in cell 'A1', then in cell 'B1' you can enter '=UniqueValsInCell(A1)', and it will return the answer. If none of this makes sense, there are countless examples on the net about getting started with VBA in Excel.

    Greg

  7. #7
    Registered User
    Join Date
    03-13-2013
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    30

    Re: Remove duplicates from single cell

    Sorry if I wasn't clear. I meant the code is too advanced for me. But I was able to use it alright! Thank you once again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Remove Duplicates and Multiple Rows into Single Cell with Commas.
    By bfitzpa in forum Access Tables & Databases
    Replies: 1
    Last Post: 06-05-2014, 07:42 AM
  2. [SOLVED] Remove duplicates in a single cell
    By SonGoku in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-27-2013, 06:35 AM
  3. Replies: 4
    Last Post: 06-06-2012, 02:15 PM
  4. remove entire row based on duplicates from single column
    By mikemeadeuk in forum Excel General
    Replies: 2
    Last Post: 10-15-2011, 02:57 PM
  5. Remove duplicates within a cell
    By forzaboro in forum Excel General
    Replies: 16
    Last Post: 08-29-2011, 10:14 AM

Tags for this Thread

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