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

Suppose you have the following dataset.

5 Methods to Find Duplicates in Excel and Copy to Another Sheet


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.
=IF(COUNTIF($B$5:$B$14,$B5)>1, "Duplicate","Unique")

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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.

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

  • 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.

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

  • 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.
=COUNTIF(B:B,B5)>1

Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet

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

  • Go to Data then Advanced Filter.

Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet

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

Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet

The results are filtered depending on the criteria range.

Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet

  • 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.

VBA to Find Duplicates and Move Rows to Another Sheet in Excel

  • 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

VBA to Find Duplicates and Move Rows to Another Sheet in Excel

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

VBA to Find Duplicates and Move Rows to Another Sheet in Excel

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

VBA to Find Duplicates and Move Rows to Another Sheet in Excel

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.

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

  • 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.

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

The duplicate data should now be formatted properly.

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

  • 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.

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

  • 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.

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

2 Comments
  1. Dear Hosne Ara:

    Thank you so very much for your help!! Your are a Rock Star!!!!

    I do have a question on the 5 best ways to check for duplicate in Excel. The #2.example:
    Excel Advanced Filter to Detect Duplicates and Copy to Some Other Sheet. Your example you state on using the COUNTIF function. …….”To do that, type the below formula in Cell E5 and hit Enter.” Well, in your example, you show it loaded in the E6 and not E5?!?!?

    I am haveing trouble to get the function to find the approate cells with the duplicate. When I tell it to go find them, it is one row off. Can you please help clear this up.

    Cant wait to hear from you and to read every article you have written.

    Waiting for your reply!!
    Bryan Kinney
    [email protected]

    • Hi Kinney, thanks for your response. The formula was actually written in cell E6. It was a typing mistake in the description of the process. We are extremely sorry for you to have trouble on this matter.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo