Solved

List repeated words in single cell excel

Posted on 2016-07-16
3
95 Views
Last Modified: 2016-07-21
Hi Experts -
In continuity with my previous question of removing duplicates, I am posting this question. I have searched on the web and found many codes to remove the single cell duplicates, but I don't want to remove but list those words using excel and vba. The conditions are as below:

1. Immediate duplicates should be listed in adjacent column.
2. If no immediate duplicates are found, but the words are repeated in the sentence, it should be also be listed but in another column.
3. To ignore certain words specified by the user as a list in another sheet only if they are not "Immediate duplicates"
4. If group of words to compare is specified result should be as shown in the attached file.

Thanks
Repeated_Values_Single_Cell.xlsx
0
Comment
Question by:Vijayendra S. Murthy
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41715006
In the string "Raw Apple Raw Apple is Very tasty.", "Raw Apple" is not immediate repeating words as Raw Apple is a sentence not a word.

BTW in the attached click the button on Sheet1 to run the macro present on Module1 to see if this is something you can work with.

Sub FindDuplicated()
Dim rng As Range, cell As Range
Dim str() As String, tstr() As String
Dim i As Long, lr As Long, j As Long
Dim ignWord As String, resStr As String
lr = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C2:C" & lr)
Range("A2:A" & lr).ClearContents
Range("D2:D" & lr).ClearContents
ignWord = Range("A9").Value
For Each cell In rng
   str() = Split(Replace(cell.Value, ".", ""), " ")
   tstr() = Split(Replace(cell.Value, ".", "") & " ^", " ")
   For i = 0 To UBound(str)
      If InStr(ignWord, str(i)) = 0 Then
         If str(i) = tstr(i + 1) Then
            If InStr(cell.Offset(0, 1).Value, str(i)) = 0 Then
               cell.Offset(0, 1).Value = cell.Offset(0, 1).Value & str(i) & " "
            End If
         Else
            If InStr(cell.Offset(0, -2).Value, str(i)) = 0 And InStr(cell.Offset(0, 1).Value, str(i)) = 0 And InStr(resStr, str(i)) = 0 Then
               If Len(Replace(cell.Value, str(i), "")) <> Len(cell.Value) - Len(str(i)) And Len(str(i)) > 1 Then
                  If resStr = "" Then
                           resStr = str(i)
                        Else
                           resStr = resStr & " | " & str(i)
                        End If
                     End If
                  End If
               End If
            End If
   Next i
   If resStr <> "" Then
      cell.Offset(0, -2).Value = resStr
   End If
   resStr = ""
Next cell
End Sub

Open in new window

Repeated_Values_Single_Cell.xlsm
0
 

Author Comment

by:Vijayendra S. Murthy
ID: 41720297
Thanks Neeraj for the macro. It is fine. I am sorry if I am asking for more but please bear with me.

1. Can this line 'ignWord = Range("A9").Value' be modified to take a complete column in another sheet but one condition is that it should consider only whole words and should ignore for words which are part of the other word. eg. Haris (is should not be ignored).

2. Can it be made case sensitive and to accept spaces. (eg. Is vs is vs " is ")

3. Please check if the 4th condition can be done.

I know that 'Raw apple' is not an immediate repeated word, but sometimes in our data 'Columbia industries Columbia industries' will be in single cell, so I asked for that specific condition. Checking the repeated words column towards left will become very difficult for large data.

Also, Can this be done using regular expressions. If possible can you incorporate the above code in one module & regex in the 2nd module for me.

Thanks for your help
0
 

Author Closing Comment

by:Vijayendra S. Murthy
ID: 41723057
@Neeraj - I will close the question as of now and select your 1st answer as best solution, but kindly go through my previous comment and help me out with this.

Thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question