Sometimes, you may duplicate texts/words while working on an Excel Worksheet. In the case of a worksheet with a large dataset, it may pose a significant challenge to identify the duplicate strings manually. However, Microsoft Excel is equipped with numerous tools that can be used for duplicate Text strings. In this post, we shall discuss common and easy ways of filtering cells with duplicate Text strings.
Using Macros to filter cells with Duplicate Text Strings
Macros are Excel tool that allows users to use code to perform some operations on the Worksheet. Here are the steps to follow while using this method to filter duplicate cells:
1. Open the Excel application.
2. Open the Excel workbook that contains the Worksheet you want to check the duplicate.
3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button. Alternatively, press the Alt + F11 keys on your keyboard to open the Visual Basic feature.
4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
5. Type the following code in the empty module.
Function Duplicates(rng As Range) As String
Dim StringtoAnalyze As Variant
Dim i As Integer
Dim j As Integer
Const minWordLen As Integer = 4
StringtoAnalyze = Split(UCase(rng.Value), " ")
For i = UBound(StringtoAnalyze) To 0 Step -1
If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA
For j = 0 To i – 1
If StringtoAnalyze(j) = StringtoAnalyze(i) Then
Duplicates = "TRUE"
GoTo SkipB
End If
Next j
SkipA:
Next i
Duplicates = "FALSE"
SkipB:
End Function
6. Press the F5 button to run the code. A Macros dialogue box will open. In the dialogue box, type the name of your macros. Then, click the Create button.
7. Close the Visual Basic screen.
8. Type the Equal sign followed by the variable you assigned in the VBA code. For example, in our case, we assigned Duplicates.
9. Select the cell index of the cell you want to check duplicates.
10. Finally, drag the formula to other cells in the Worksheet.
Using Visual Basic to highlight Duplicate Text Strings
Steps:
1. Open the Excel workbook that contains the Worksheet you want to check the duplicate.
2. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button.
3. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.
4. Type the following code in the empty module.
Public Sub HighlightDupesCaseInsensitive()
Dim Cell As Range
Dim Delimiter As String
Delimiter = InputBox("Enter the delimiter that separates values in a cell", "Delimiter", ", ")
For Each Cell In Application.Selection
Call HighlightDupeWordsInCell(Cell, Delimiter, False)
Next
End Sub
Sub HighlightDupeWordsInCell(Cell As Range, Optional Delimiter As String = " ", Optional CaseSensitive As Boolean = True)
Dim text As String
Dim words() As String
Dim word As String
Dim wordIndex, matchCount, positionInText As Integer
If CaseSensitive Then
words = Split(Cell.Value, Delimiter)
Else
words = Split(LCase(Cell.Value), Delimiter)
End If
For wordIndex = LBound(words) To UBound(words) – 1
word = words(wordIndex)
matchCount = 0
For nextWordIndex = wordIndex + 1 To UBound(words)
If word = words(nextWordIndex) Then
matchCount = matchCount + 1
End If
Next nextWordIndex
If matchCount > 0 Then
text = ""
For Index = LBound(words) To UBound(words)
text = text & words(Index)
If (words(Index) = word) Then
Cell.Characters(Len(text) – Len(word) + 1, Len(word)).Font.Color = vbRed
End If
text = text & Delimiter
Next
End If
Next wordIndex
End Sub
5. Press the F5 button to run the code.
6. Finally, close the Visual Basic screen.
