How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)

Suppose you have the following dataset.

Method 1 – Use an Excel Formula to Get Duplicates and Copy to Different Sheet

Steps:

• Add a helper column (Status in this example) and enter the below formula in the first cell of that column (D5).
• Press Enter.

The COUNTIF function counts the number of cells in column B where the cell value is equal to Cell B5.

The IF function returns Duplicate if the given condition (>1) is met, otherwise it returns Unique.

• Use the Autofill Tool to copy the formula to the remaining cells in the column.

• To filter the data, select any cell in the dataset, and go to the Data ribbon then Filter.

• Each column is now available for filtering. Click on the drop-down arrow of the helper column and put a checkmark only for the Duplicate option.

• All the values that have a ‘Duplicate’ status are now filtered. Copy the result by pressing Ctrl + C.

• Paste the copied data into the second sheet (Sheet2) using any of the Paste Options or simply by pressing Ctrl + V.

Read More: How to Find Duplicate Rows in Excel

Method 2 – Use the Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet

Steps:

• Type the below formula in an appropriate cell (E5).
• Hit Enter.

The results should show True if the data shows more than once in the above list (B5:B14).

• Go to Data then Advanced Filter.

• The Advanced Filter window will appear. Specify the List range, Criteria range, and Copy to location.
• Press OK.

The results are filtered depending on the criteria range.

• Copy the cells (E8:F12) and paste them into the new sheet (SheetB).

⏩ Note:

While applying the Advanced Filter, make sure the Criteria range contains a range of cells. In this example, a blank cell (E5) is included along with cell E6 to make it a range.

Read More: How to Find Repeated Cells in Excel

Method 3 – Use VBA to Find Duplicates and Move Rows to Another Sheet in Excel

Steps:

• Right-click on the sheet name and select View Code to bring up the VBA window.

• Type the code below in the Module and run the code using the F5 key.

Sub MoveDuplicates()

Dim rng As Range
Dim rng1 As Range
Dim X As Long, Y As Long
On Error Resume Next
Set rng = Application.InputBox("Please select the source column:", "Microsoft Excel", Selection.Address, , , , , 8)
If rng Is Nothing Then Exit Sub
Set rng1 = Application.InputBox("Please select the destination cell:", "Microsoft Excel", , , , , , 8)
If rng1 Is Nothing Then Exit Sub
nRows = rng.Rows.Count
Y = 0
For X = nRows To 1 Step -1
If Application.WorksheetFunction.CountIf(rng, rng(X)) > 1 Then
rng(X).EntireRow.Copy rng1.Offset(Y, 0)
rng(X).EntireRow.Delete
Y = Y + 1
End If
Next

End Sub

• Once you run the code, an input box will appear.
• Enter the column range (A2:A11).
• Press OK.

• In the next input box, specify the destination cell (A2  from SheetY).
• Press OK.

The duplicated data should show in the new sheet.

Read More: How to Find Repeated Numbers in Excel

Method 4 – Apply Conditional Formatting to Search Duplicates and Later Copy to a Different Sheet in Excel

Steps:

• Select the relevant data in the current sheet.
• Go to the Home ribbon and then Conditional Formatting.

• From the Conditional Formatting drop-down, choose Highlight Cells Rules then Duplicate Values.

• The Duplicate Values dialog box will appear.
• Select the highlight color as you want and press OK.

The duplicate data should now be formatted properly.

• Select and copy all the cells that are duplicates.

• Paste the copied values to the new sheet (Sheet2).
• Reformat the highlight color if necessary.

Read More: How to Filter Duplicates in Excel

Method 5 – Find Duplicates and Copy to Another Worksheet with an Excel Pivot Table

Steps:

• Select any cell in the dataset, and go to the Insert ribbon, then Tables.
• Choose Pivot Table and From Table/Range.

• Check the Table/Range, specify the Location in the PivotTable from table or range dialog, and press OK.

• Select the newly created Pivot Table, and the Pivot Table Fields dialog will appear.
• Drag the appropriate field (Fruits) into both the Rows and the Values area.

• If the field count (Count of Fruits) is equal to or greater than 2, those values are duplicates.

• Click on the filter drop-down of Row Labels and go to Value Filters then Greater Than Or Equal To.

• The Value Filter dialog appears. Enter 2 and press OK.

• Select the duplicates from the dataset (B4:C14) and copy them.
• Paste the copied values into another sheet (Sheet2).

Read More: How to Compare Rows for Duplicates in Excel

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Related Articles

• Excel Find Duplicate Rows Based on Multiple Columns
• How to Compare Two Excel Sheets for Duplicates
• How to Find Matching Values in Two Worksheets in Excel
• Excel VBA to Find Duplicate Values in Range
• How to Find Duplicates in a Column Using Excel VBA
• How to Use VBA Code to Find Duplicate Rows in Excel

<< Go Back to Find Duplicates in Excel | Learn Excel