How to find same mail id in excel

To find duplicate email addresses in Excel, you can follow these steps:

Method 1: Using the COUNTIF function

1. Select the range of cells that contains the email addresses.
2. Go to a new column next to the email addresses.
3. Enter the formula: =COUNTIF(A:A, A2)>1 (assuming the email addresses are in column A).
4. Press Enter to apply the formula.
5. This will return a value of 1 if the email address is unique, and a value greater than 1 if it's a duplicate.
6. Filter the column to show only the rows with a value greater than 1 to find the duplicate email addresses.

Method 2: Using the Advanced Filter

1. Select the range of cells that contains the email addresses.
2. Go to the "Data" tab in the ribbon.
3. Click on "Advanced" in the "Data Tools" group.
4. In the "Advanced Filter" dialog box, select "Copy to another location".
5. Enter the criteria: Email Address is equal to Email Address.
6. Click "OK" to apply the filter.
7. This will copy the duplicate email addresses to a new location.

Method 3: Using the Remove Duplicates feature

1. Select the range of cells that contains the email addresses.
2. Go to the "Data" tab in the ribbon.
3. Click on "Remove Duplicates" in the "Data Tools" group.
4. In the "Remove Duplicates" dialog box, select the column that contains the email addresses.
5. Click "OK" to apply the feature.
6. This will remove all duplicate email addresses, leaving only unique ones.

Method 4: Using a VBA Macro

1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to "Developer" tab in the ribbon.
2. Create a new module by clicking "Insert" > "Module".
3. Paste the following code:

   Sub FindDuplicates()
    Dim rng As Range
    Set rng = Range("A:A") ' adjust the range to your email addresses
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    For Each cell In rng
        If dict.Exists(cell.Value) Then
            dict(cell.Value) = dict(cell.Value) + 1
        Else
            dict.Add cell.Value, 1
        End If
    Next cell
    For Each key In dict
        If dict(key) > 1 Then
            MsgBox "Duplicate email address: " & key
        End If
    Next key
   End Sub

4. Save the module by clicking "File" > "Save" (or press Ctrl + S).
5. Run the macro by clicking "Run" > "FindDuplicates" (or press F5).

These methods will help you find duplicate email addresses in Excel.