+ Reply to Thread
Results 1 to 8 of 8

Need help removing duplicate text values, space comma separated, from cells within a colum

  1. #1
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    Post Need help removing duplicate text values, space comma separated, from cells within a colum

    Hi there;

    I am new to this forum and this is my first post. I am looking for a way to remove duplicate values that are contained within a cell, in an entire column of my workbook. It's breaking a Vlookup I am doing for a project at work. some cells have multiple and different values (which is okay) but there are some that have multiple( same values) that I'd like to reduce to one value.

    I have attached a sample of the data i am working with and have highlighted cells that have multiple / same values that I would like reduced to single value.

    Appreciate any ideas on this.
    Attached Files Attached Files

  2. #2
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    My solution would, unfortunately, use a number of helper columns. If that would work for you, I will proceed.

  3. #3
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    I would be interested in your approach. Hoping there might be a Macro that would solve for this, but those I've see so far do
    not appear to work. Can you send me info on your helper column approach and I'll try that first.

    Thanks

  4. #4
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    Yes, you could probably do this with macros, but that would take [me, anyways] more time. Hope this works for you (I hid the offending helper columns).

    The solution assumes:
    • You have up to five entries per line; and
    • Any of the five could repeat, not just the first entry.
    Making it work for more than five entries is, of course, possible following the same approach.

    It's clunky for sure but appears to work in all cases. I did modify one or two entries as part of the testing ...
    Attached Files Attached Files

  5. #5
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    Of course, you can aways use this approach, turn the results column into text, and then delete the helper columns. That would essentially get you to where you would be if you used macros.

    I forgot to mention that my formulas don't "clean up" after themselves, so you may have duplicate commas and so forth. If that's an issue, let me know.

  6. #6
    Registered User
    Join Date
    02-09-2013
    Location
    Seattle, Washington
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    this is helpful thank you. Can you easily talk me through how to do this? I'd like to learn for future uses.

    Thanks

  7. #7
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    Columns B through F determine the ending positions of each entry.
    Column G determines how many items in the list for that line.
    Columns H through L determine the length of each item.
    Columns M through V compare each combination; a "1" means there's a duplicate.
    Columns W through AF eliminate duplicates, in reverse order from Columns M through V. This is done that way because if you started from the left, all the starting/ending positions would change each time and you'd have to keep track. This approach avoids that.

    The formulas themselves are relatively straightforward, but if you have additioanl questions, please let me know.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,545

    Re: Need help removing duplicate text values, space comma separated, from cells within a c

    Here's a macro sol'n
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

+ 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