Deleting duplicate rows but preserving & moving one column

**krenejones** · 11-10-2009, 01:54 PM

Hi All,

I would like to know if there is a formula that will remove all duplicate rows. However, there is one column of data that is not duplicated, and I would like to preserve that data and move it into a new row.

Here is an example:

12361646 AvisRentACar A 7811 Waynetowne Blvd Dayton OH Restaurants
12361646 AvisRentACar A 7811 Waynetowne Blvd Dayton OH Auto Rentals
12361646 AvisRentACar A 7811 Waynetowne Blvd Dayton OH Auto Leasing
12361646 AvisRentACar A 7811 Waynetowne Blvd Dayton OH Furniture Rental

to become

12361646 AvisRentACar A 7811 Waynetowne Blvd Dayton OH Restaurants Auto Rentals Auto Leasing Furniture Rental

**mrice** · 11-10-2009, 03:53 PM

Assuming that your data has a header row and the column to move is E then the following macro should work.

Sub Test()
For N = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(N, 1) = Cells(N - 1, 1) Then
        FirstRow = Columns(1).Find(Cells(N, 1), Cells(1, 1), xlValues, xlWhole).Row
        Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(N, 5)
        Rows(N).Delete
    End If
Next N
End Sub