# Copy Rows With Duplicate ID Code

## How to copy all rows and the corresponding data for all ID codes that are duplicates in Excel

In this Excel example tutorial I will show how we can copy provided certain rows to other specific worksheets. This Excel tutorial contains two variants solutions. The first part of the tutorial solution using the formula. Another part of the solution is using VBA macro.

You notice the problem situation in the image below. The columns have a specific item in the structure we have ID codes and the corresponding data for that ID code. Each ID code appears multiple times. So we have a duplicate ID codes. Our goal is to copy data to specific other worksheets, if the condition number of occurrences of a particular ID code. In the picture below you see the other specific worksheets on which we want to have the results of all the duplicates that appear a number of times. So you see that we have the names of the worksheets (1 ID, 2 ID, 3 ID, 4 ID, 5 ID and 6 ID). The worksheets have a number in its name and therefore we define how certain duplicates.

eg 1. If an ID code appears 2x then the respective ID code and all associated data from the same order we need to copy the worksheet "2 ID".

eg 2. If an ID code appears 4x then the respective ID code and all associated data from the same order we need to copy the worksheet "4 ID". ## How to Copy All Rows that Contain the Same Code Numbers or Values

Solving this problem can be carried out using the formula for counting the occurrence of certain ID codes. I am, before the table, added one column in which I set up a formula that will count how many times it appears certain ID code in the 'B' column.

The formula in cell 'A2' is as follows: (this formula can be used if you want some other requirement that the copy rows. Eg. If you want to copy to the number of occurrences of data in the D column, then we need to change the formula).

(count the number of occurrences in the B column)

=COUNTIF(\$B\$2:\$B\$27;B2)

or

(count the number of occurrences in the D column)

=COUNTIF(\$D\$2:\$D\$27;D2) If you want, you can reduce the time to set up the conditions, in this case we will use the Data Validation to set the drop-down list of conditions. So in a cell 'A1' on the worksheet "master" to enter a list of conditions B, C, D, E

In cell 'A2' set the following formula (a copy to the last row)

=COUNTIF(INDIRECT("\$"&\$A\$1&"\$"&2&":"&"\$"&\$A\$1&"\$27");INDIRECT(\$A\$1&ROW(A2)))

In this way, it is sufficient to select a column as a condition drop-down list in the cell 'A1' and the formula will automatically count the items from the list and thus automatically copy all rows in a given condition to the other worksheets.

## Copy Duplicates Provided on Another Worksheet Using the Excel Formulas

In the picture below you see that I have on each worksheet column added in front of the table and he did the following.

In the cell 'A1' I have set up a formula that returns a number from the name of the active worksheet. This number is a requirement for further calculation, which means that on the active worksheet, Excel should display all rows from a worksheet "master" repetitive 2x. Also on the worksheet "4 ID" is the result of the number '4' which is a requirement that we Excel displays all rows from a worksheet "master" containing the ID code that is repeated 4x. etc etc ...

=VALUE(LEFT(MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;256);1))

ARRAY formula in cell 'A2' is as follows. (This CSE formula us as a result returns a list of orders or data from the respective rows of the worksheet "master" if the condition of the cell' A1 '. Copy a formula to the right until the last column and then down

=IFERROR(INDEX(master!\$A\$2:\$E\$27;SMALL(IF(\$A\$1=master!\$A\$2:\$A\$27;ROW(master!\$A\$2:\$A\$27)-MIN(ROW(master!\$A\$2:\$A\$27))+1; ""); ROW(A1));COLUMN(A1));"") ## How to copy the rows provided using VBA macro

Solution VBA - Option 1

This problem can be solved using VBA macro. The following VBA macro copy in your VBE for Excel and start using VBA button.
The first VBA macro that cleans or deletes existing data range on the worksheets, this VBA macro is below. This VBA macro to copy the standard Module1.

Private Sub ClearRange()
'Clear Range on Multiple Sheets
On Error Resume Next
With Worksheets("1 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("2 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("3 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("4 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("5 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
With Worksheets("6 ID")
.Range("A2:E100").SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub

Second VBA macro that copies all rows that meet the condition, as follows. For this VBA macros you create VBA button on the worksheet "master". This VBA macro to copy the standard Module1

Run this VBA macro and Excel will copy all rows that contain a requirement to 'A' column.

Sub CopyAllRowsIfMatchCondition()
'Sheet master Button1
'Copy rows if match condition from "master" sheet to another specific sheets to A column
Call ClearRange 'calling clear procedure
Dim i, LastRow
LastRow = Sheets("master").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("master").Cells(i, "A").Value = 1 Then 'condition number 1
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("1 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 2 Then 'condition number 2
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("2 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 3 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("3 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 4 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("4 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 5 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("5 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
If Sheets("master").Cells(i, "A").Value = 6 Then
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("6 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
Range("A1").Select
End Sub

Solution VBA - Option 2

There is another option, or variants of solutions using VBA macro. Here's another Excel example

In the Module2 copy this VBA macro below. This VBA macro copies the rows that meet the conditions set out in column A in the worksheet "master". This VBA macro below, multiply six times one below the other and change the number of conditions. Note the name of the source and the destination worksheet (bold letters).

Sub CopyRowsWithCondition1()
'Copy Rows Data for Condition in A column
Dim i, LastRow
LastRow = Sheets("master").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("1 ID").Range("A2:E100").ClearContents
For i = 2 To LastRow
If Sheets("master").Cells(i, "A").Value = 1 Then 'condtion number 1
Sheets("master").Cells(i, "A").EntireRow.Copy Destination:=Sheets("1 ID").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Also, in the Module2 set and this VBA macro below. The VBA procedure calls all the individual procedures to copy the lines that contain a requirement to 'A' column. Clicking on the button VBA Excel will automatically copy all rows that contain the specified criteria in a specific worksheet.

Sub CopyRowsWithConditionAll2()
'calling all certain procedures in Module2
'Sheet "master" Button2
Call ClearRange 'calling clear procedure
Call CopyRowsWithCondition1
Call CopyRowsWithCondition2
Call CopyRowsWithCondition3
Call CopyRowsWithCondition4
Call CopyRowsWithCondition5
Call CopyRowsWithCondition6
Range("A1").Select
End Sub

To be clear, this above single VBA procedure may be used for each destination worksheet individually using VBA buttons, so we invite directly to the destination worksheet. And finally, of course, experts in programming VBA for Excel this would solve the easier or different way, but from me this is enough.

I hope that I have managed to explain how we can solve a number of ways to copy all the rows that contain a specific requirement.