how to show if a table contains the same data

  • Thread starter Thread starter Heine
  • Start date Start date
H

Heine

Hi everybody,

has anybody any idea of how to somehow highlight or show if I have the
same value twice or more times in a column?

For instance

1 Tom
2 Dick
3 Harry
4 Tom


Then I would like to somehow hightlight Tom. Problem is I have a lot
of data I want to search through.

Any thoughts?

Best regards
Heine
 
Format/ Conditional Formatting/ Formula is/ =COUNTIF($B$1:$B$4,$B1)>1, then
choose your highlighting format.
 
Heine,

here is a macro I wrote that will select all duplicates. It differs from
other approaches, which mostly rely on the COUNTIF function, because it
1) ignores the first instance (which I think can't be called a duplicate)
2) works on multiple selections (may not be interesting for you, but I need
that functionality in some cases).

Cheers,

Joerg Mochikun



Sub SelectDuplicates()
'Macro selects all duplicates (=second and more instances of same text or
value) either within selection or
'(if only 1 cell selected) in whole sheet.
'Hidden cells are ignored, selection of multiple ranges is supported
'Macro uses Regular Expressions and therefore requires a recent version of
Windows Scripting Host to be installed

Dim rng, MyArea As Range
Dim TotalCellValues, SearchString, i As String

Dim RegExp As Object
Set RegExp = CreateObject("VBScript.RegExp")

i = 0
If Selection.Cells.Count = 1 Then
Set rng = ActiveSheet.UsedRange
Else
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
End If

For Each cell In rng
If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then GoTo
EndForLoop
SearchString = "@" & cell.Value & "@"
TotalCellValues = TotalCellValues & "@" & cell.Value & "@"
RegExp.Pattern = "(@" & cell.Value & "@.*){2,}"
If RegExp.test(TotalCellValues) Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
End If


EndForLoop:
Next cell
If MyArea Is Nothing Then
MsgBox "No duplicates found!"
Else
MyArea.Select
End If

End Sub
 
Back
Top