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

In this article, I will discuss how you can find duplicates in Excel and copy them to another sheet. While working with a large number of data in Microsoft Excel, often you will find duplicate values. Besides, when we combine data from various sources data duplication is a very common scenario. Sometimes we need to detect duplicate values in Excel so that we can understand the occurring frequency of a certain value. After we detect the duplicates, we can copy or move them to another Excel worksheet. So, let’s go through the article.

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

Suppose, in an Excel sheet, I have a dataset containing several fruit items along with their sold quantities. There are some fruits that are present more than once. Now I will find duplicate fruits items from the below dataset and copy them to another Excel worksheet following 5 methods.

1. Use Excel Formula to Get Duplicates and Copy to Different Sheet

First of all, I will use a combination of COUNTIF and IF functions to detect duplicates. Later I will apply the Filter option to get all the duplicate items. Suppose my dataset is located in Sheet1. Now, follow the below steps to find duplicates and copy them to Sheet2.

Steps:

• First I will add a helper column ‘Status’ to my original dataset. To get the status of each fruit, type the below formula in Cell D5. Next press Enter.

Here, the COUNTIF function counts the number of cells in column B where the cell value is equal to Cell B5. Later, the IF function returns Duplicate if the given condition (>1) is met, otherwise it returns Unique.

• Upon applying the formula and the Fill Handle (+) tool. We will get the below output.

• As we received the status of each fruit item, now we will filter ‘Duplicate’ data. To apply the Filter, select any cell in the dataset, and go to Data > Filter.

• As a result, the filtering drop-down icon shows up. Click on the drop-down arrow of the Status column and put a checkmark only for the Duplicate option.

• Consequently, we will see that all the values that have a ‘Duplicate’ status are filtered as below. Copy the result by pressing Ctrl + C.

• Finally, paste the copied data in Sheet2 using any of the Paste Options or simply by pressing Ctrl + V.

Read More: How to Find Duplicate Rows in Excel

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

In this method. I will use the Advanced Filter to find duplicate values. And we already know that once you get duplicates, copying them to another sheet is just a matter of time. Follow the below steps to get the duplicates using the Advanced Filter option in Excel.

Steps:

• Suppose I have the source data in SheetA. First of all, I will determine the criteria range using the COUNTIF function. To do that type the below formula in Cell E5 and hit Enter.

• Consequently, we will get the below result showing a given fruit’s occurrence status by returning True/False. The above formula returns True if the entered fruit is present more than once in the above list (B5:B14).

• Now go to Data > Advanced Filter.

• As a result, the Advanced Filter window will appear. Now, specify the List range, and Criteria range, Copy to location and press OK.

• Then we will get the following result. All the duplicate fruits are filtered depending on the criteria range.

• After that, you can copy these cells (E8:F12) and paste them into SheetB as we wanted.

⏩ Note:

While applying Advanced Filter, make sure, Criteria range contains a range of cells. Here I have included a blank cell (Cell E5) along with Cell E6 to make it a range.

Read More: How to Find Repeated Cells in Excel

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

This time I will find duplicates from a column of my dataset using VBA. Afterward, I will move the entire row that contains duplicates.

Steps:

• First, go to SheetX which has the original data. Next right-click on the sheet name and select View Code to bring up the VBA window.

• Now type the below code 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 the below input box will appear, enter the column range (A2:A11) as it demands, and press OK.

• Then another input box will want you to specify the destination cell (where you want to move duplicate values). I have selected Cell A2  from another SheetY as my destination cell. After that, press OK.

• Finally, we will get the below result in SheetY.

Read More: How to Find Repeated Numbers in Excel

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

We can highlight duplicates using Conditional Formatting in Excel. Later, we can copy highlighted data to a different Excel sheet.

Steps:

• First, select all the fruits of our dataset in Sheet1, and go to Home > Conditional Formatting.

• Next, from the Conditional Formatting drop-down go to Highlight Cells Rules > Duplicate Values.

• As a result, the Duplicate Values dialog came up. Select the highlight color as you want and press OK.

• Once you press OK, all the duplicate fruit items are highlighted in blue as shown in the below screenshot.

• After that, simply select and copy all the cells that are duplicates.

• Finally, paste the copied values to Sheet2. You can delete the highlight colors now if you want.

Read More: How to Filter Duplicates in Excel

5. Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

In this method, I will find out duplicate values using the Pivot Table in Excel. Follow the below instructions to get the expected result.

Steps:

• Initially, I will insert a Pivot Table from the dataset of Sheet1. To do that, select any cell in the dataset, and go to Insert > Tables > Pivot Table > From Table/Range.

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

• Consequently, a Pivot Table will be inserted. Now, select the Pivot Table, and the Pivot Table Fields will appear. Now drag the ‘Fruits’ field in both the Rows and Values area.

• After that, you can see the Pivot Table below. From the table, we can see a list of fruit items along with their count of occurrence. If the ‘Count of Fruits’ is equal to or greater than 2 that means those values are duplicates.

• Now,  to get the filtered list of duplicate items, click on the filter drop-down of Row Labels and go to Value Filters > Greater Than Or Equal To.

• As a result, the Value Filter dialog appears, enter 2 there and press OK.

• Finally, we will get the list of duplicates in Sheet1. As we have found out which items are duplicates, we can select them from the dataset (B4:C14). Later we can paste the copied values into another excel sheet (say, in 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.

Conclusion

In the above article, I have tried to discuss several methods to find duplicates and copy them to another sheet in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

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 | Duplicates in Excel | Learn Excel