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.


Download Practice Workbook

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


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

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.

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


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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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.

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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

Use Excel Formula to Get Duplicates and Copy to Different Sheet

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


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

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

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

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

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

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

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

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

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


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.

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

  • 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

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

  • Once you run the code the below input box will appear, enter the column range (A2:A11) as it demands, and press OK.

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

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

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

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


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.

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

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

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

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

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

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


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.

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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

Find Duplicates and Copy to Another Worksheet with Excel Pivot Table

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


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

1 Comment
  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]

Leave a reply

ExcelDemy
Logo