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

Question

Hello I have an excel file and I wanna look for the duplicate values in a certain field like a list of email accounts. Like making them to be formatted the same or something like that.
Do you know how to that ?

Answer 1

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.

Answer 2

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