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