1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Help Req: How to remove duplicate words in a row

Discussion in 'Business Applications' started by ayush, Feb 27, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Excel Help Request How to remove duplicate words in a row

    Example:
    Row1:
    I am writting to tech support guy tech support for help
    Required result: I am writting to tech support guy for help

    Row2:
    I am sure I will am sure get help
    Required result: I am sure I will get help

    Row3: .....
    Row4: .....

    Thanks in advance
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    http://www.microsoft.com/en-gb/download/details.aspx?id=15011 might be of use


    I can foresee problems as duplication is not always a bad thing.


    Your Row 2 example demonstrates that:


    I am sure I will get help


    Could you explain a little about where the duplication is coming from. It sounds to me like one or more fields are getting joined, introducing duplication.
     
  3. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Sir,
    Thanks for reply. The MS addon link is not useful.
    I got database of addresses. In the address field I got duplication of words.
     
  4. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    OK, so how many words is the longest address?
     
  5. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Sir, It is 15 words maximum
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    I would suggest a sample file, I think that that would explain it better since.
    The number of words duplicated, can it also be that one word occurs 10 times or more?
    You say row, but how many columns? or is it cells per row that conpatai n duplaicat words?
    Do the words without the duplicates still make sense or is it just a question of just wanting to keep a list of unique words per row.
    I have an idea but then I'd need to know what you have and what you really need as a result.
    Your answers are (bbut that is my opnion) quite short an not very clear
     
  7. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Sir,
    Sample file attached. No. of columns are less than 6.
    Row are few thousands.
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    I think I can write a function that will remove duplicate text but the result may not alwyas be what you want.

    For example:
    14TH ST., TANSI NAGAR, TANSI NAGAR 14TH ST,

    This would result in something like
    14TH ST., TANSI NAGAR,

    Another one:
    100 FEET BYE PASS ROAD, 100FEETBYPASS ROAD,

    Examples like this one will be hard to correcly work out
    100 FEET BYE PASS will not find 100FEETBYPASS

    and if you only take the word PASS will work for this example but if the word PASSAGE would accour and the PASS would be removed, then the result would be AGE

    You will have to make sure you have a list of exceptions.

    Will this be a one-time run the moment you import the address list?

    And there are more of these similiar combinations.

    You must think about all this before me or anybody else takes up this 'challenge'
     
  9. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    This is the problem - you would want a non-destructive process which allows you to compare the original string with the proposed de-duped string. Although you could automate it to a degree, you would still need qualitative checking - which is a human function.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    I agree, this is a typical case of 'human intervention required!'
    But an addtional X-check to accept 'logical' de-dups could take care of part of the processing.
    Yes, an interface where the user can either click for accept or enter an alternative result could be an option.
     
  11. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    I sincerely thanks for the keen interest of Keebellah & CodeLexicon to solve my problem.
    I have noted the limitations mentioned by Keebellah. Please go ahead sir.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    You will have to be patient, I do this in my spare time and will update when I have something ready to show.
     
  13. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    A basic way to remove duplicate words.

    Code:
    Function DuplicateCheck(str As String) As String
        Dim arr As Variant
        Dim i As Integer, j As Integer
        
        arr = Split(str, " ")
        If UBound(arr) < 1 Then Exit Function
        
        For i = 1 To UBound(arr)
            For j = 0 To i - 1
                If arr(j) = arr(i) Then
                    arr(i) = ""
                    Exit For
                End If
            Next
        Next
        DuplicateCheck = Join(arr, " ")
        DuplicateCheck = Application.Substitute(DuplicateCheck, "  ", " ")
    End Function
     

    Attached Files:

  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Hi Garf13ld, this is works nicely (y)
    The poster will have to do some extra cleaning for the exceptions like ST. ST en E B COLONY and EB COLONY
    As far as I can see your UDF covers most of the issues
     
  15. ayush

    ayush Thread Starter

    Joined:
    Mar 27, 2005
    Messages:
    86
    Thankyou verymuch

    Garf13LD
    Keebellah
    CodeLexicon
    [​IMG]
     

    Attached Files:

    • tsg.png
      tsg.png
      File size:
      22.9 KB
      Views:
      89
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1120899

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice