Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Copy Rows with duplicate ID Code

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

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

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 code. Our goal is to copy data to specific other worksheets if the condition number of occurrences of a particular ID code.

How to copy all the rows that contain the same 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".

Copied rows that contain duplicate conditions

How to copy all rows that contain the same code

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) 

Copying rows provided in column
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 formula
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 certain rows that contain the using formula

How to copy the rows provided using VBA macro


------------------- Solution VBA - Option 1 ---------------------

The first VBA macro that cleans or deletes existing data range on the worksheets, this VBA 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 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.