how to show if a table contains the same data
 

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

how to show if a table contains the same data
 

Format/ Conditional Formatting/ Formula is/ =COUNTIF($B$1:$B$4,$B1)1, then
choose your highlighting format.
--
David Biddulph

"Heine" wrote in message
ups.com...
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

how to show if a table contains the same data
 

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









"Heine" wrote in message
ups.com...
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