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.
=IF(COUNTIF($B$5:$B$14,$B5)>1, "Duplicate","Unique")
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.
=COUNTIF(B:B,B5)>1
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
Get FREE Advanced Excel Exercises with Solutions!
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.