How can I look for the duplicate values in a certain field in a spreadsheet?

I have an Excel file and want to look for the duplicate values in a ce开发者_如何学Pythonrtain field like a list of email accounts. Like making them to be formatted the same or something like that.

How to do that?

When using Excel version 2007, built functionality can be used to remove duplication.

The command is on tab Data, a group Data Tools;
icon Remove duplicates.

Or use a macro. To mark duplicated values by the tag, such as X to an adjacent column. Next, they can be used by auto filter, to filter out the rows marked and by keyboard shortcuts CTRL + - (minus key) duplicates can be removed at once.

Option Explicit
'crea by pc-prog.eu

Sub SelectRowOfDupli_A()
Dim x As Variant, xRng As Range, xR As Range
Dim xMltRow As String, i As Integer, xObl As String
Set xRng = Selection
x = "xxxxxx"

On Error GoTo xErr
xObl = "B"
xObl = InputBox("Enter COLUMN where by 'X' sign will be marked duplicate entries " & _
"of selected cells:", "RANGE", xObl)

If xObl = "" Then
MsgBox "Column name must be entered!", vbCritical, "CHYBA"
Else
For Each xR In xRng
If Trim(CStr(xR.Value)) = x Then
Range(xObl & CStr(xR.Row)).Value = "X"
i = i + 1
Else
x = Trim(CStr(xR.Value)) 'xR.Value
End If
Next xR
If xMltRow <> "" Then
Range(xMltRow).Select
End If
MsgBox "Done. " & CStr(i) & " duplicates."
End If
Exit Sub
xErr:
MsgBox Err.Description, vbCritical, "FINISHED WITH ERRORS:"
End Sub

You can find duplicates in a column by using making another column containing the formula (assuming you're checking column Q) COUNTIF(Q:Q, Q2).

You can then use conditional formatting to highlight rows where the new column's value is > 1.