Copy, Paste then delete
 

Hello
I have a data sheet that has sorted values in ColA:D. For each line on the
list I would like to compare the values in ColB and ColD to the values in the
next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and
then delete Row2.
Example

Dog 123456 Blue 500
Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Would become:
Dog 123456 Blue 500 Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Thanks for all replies
Louise

Copy, Paste then delete
 

This works for me. Save your data before testing.

Sub MoveData()

Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _
And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1,
3).Value Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value
ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value
ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

Regards
Rowan

"Louise" wrote:

Hello
I have a data sheet that has sorted values in ColA:D. For each line on the
list I would like to compare the values in ColB and ColD to the values in the
next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and
then delete Row2.
Example

Dog 123456 Blue 500
Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Would become:
Dog 123456 Blue 500 Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Thanks for all replies
Louise

Copy, Paste then delete
 

Lookout for the wrapping of line 4. Should be more like

Sub MoveData()

Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _
And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1, 3).Value Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value
ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value
ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

Regards
Rowan

"Rowan" wrote:

This works for me. Save your data before testing.

Sub MoveData()

Range("A2").Select
Do Until ActiveCell.Value = Empty
If ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value _
And ActiveCell.Offset(0, 3).Value < ActiveCell.Offset(1,
3).Value Then
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(1, 0).Value
ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(1, 1).Value
ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(1, 2).Value
ActiveCell.Offset(0, 7).Value = ActiveCell.Offset(1, 3).Value
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

Regards
Rowan

"Louise" wrote:

Hello
I have a data sheet that has sorted values in ColA:D. For each line on the
list I would like to compare the values in ColB and ColD to the values in the
next line. If B1=B2 and D1<D@ then I would like to copy A2:D2 to E1:H1 and
then delete Row2.
Example

Dog 123456 Blue 500
Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Would become:
Dog 123456 Blue 500 Dog 123456 Red 400
Dog 234123 Blue 500
Dog 234123 Blue 500

Thanks for all replies
Louise