Identical rows based on column A.

[mathsbeauty]

Hello! I am learning macros in excel. I need macro to feet the following situation.
The column A contains numbers. If the number is repeated in this column then it should copy the row which contains the number and paste that row in the cell in which the number is repeated. The situation may look complex but it is not. The problem is simply that "The rows should be identical if they contain the same entries in column A."

 

[JackDanIce]

It's not clear what you mean. If you have the following:
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD>A</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD>B</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD>D</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">4</TD><TD style="BACKGROUND-COLOR: #ffff00">D</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD>G</TD></TR></TBODY></TABLE>
I'm assuming you want the highlighted row copied, but what do you mean by "paste that row in the cell in which the number is repeated"?

The more accurate and concise a question you ask, the easier it is to understand and provide an answer

 

[mathsbeauty]

Sorry JackDanIce. I might not have phrased my question properly (because I am not good in English). Please try to understand my problem. In your example suppose the 5th row is "4 E" instead of "4 D". Now the situation is:
4th row is "4 D" and 5th row is "4 E". I want to copy 4th row and paste (replace) it into 5th row. Then the rows 4 and 5 will be identical. Does this make sense?
The rows should be identical if they contain same value in column A.

 

[JackDanIce]

Identical rows based on column A

Makes more sense. In that case, try:

Sub MooMoo ()

Dim i as Long

For i = 1 to Range("A" & Rows.Count).End(xlUp).Row
  If Range("A" & i) = Range("A" & i + 1) Then
    Range("A" & i).Copy)
    Range("A" & i + 1).PasteSpecial xlPasteValues
  End If
Next i

End Sub

Let me know if still a problem.

 

[mikerickson]

Re: Identical rows based on column A

Perhaps

Sub test()
    Dim oneCell As Range
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        For Each oneCell In .Cells
            oneCell.EntireRow.Value = Rows(Application.Match(oneCell.Value, .EntireColumn, 0)).Value
        Next oneCell
    End With
End Sub

 

[JackDanIce]

****, beat me!

 

[mathsbeauty]

Hello JackDanIce! First of all thanks for all your efforts. Your code seems to work only for adjacent rows. It doesnt work for non-adjacent rows. e.g. if 4th row is to be copied and paste it into 7h row, then it won't work. Thanks

 

[mathsbeauty]

Hello Mikerickson! It works great. You are awesome! One more thing I want to ask. Is it possible to modify this macro in the following way:
The entire row is copied and pasted but the only values in the column B remain unchanged. The problem is to have the identical rows based on column A except for the fact that the values in column B remain as it is. Thanks

 

[mathsbeauty]

Any suggestions please for the problem to have the identical rows based on column A except for the fact that the values in column B remain as it is.

 

[mikerickson]

If you have 102 or fewer columns of data, this should preserve the column B differences.

Sub test()
    Dim oneCell As Range
    With Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
        For Each oneCell In .Cells
            oneCell.EntireRow.Resize(1,100).Offset(0,2).Value = Rows(Application.Match(oneCell.Value, .EntireColumn, 0)).Resize(1,100).Offset(0,2).Value
        Next oneCell
    End With
End Sub