How to remove duplicate columns with rows

[tonynguyen10]

Hello,

I am using Excel 2016 on a Mac. When I used to have a Windows months ago, when I removed duplicates in a column, the rows would move with them which kept all the information together. Now, when i go to remove the duplicates, the rows stay and the column shifts which makes the information break apart.

To give you insight, I'm working with a list of people, their investment property and their home mailing address. If John Smith owns 3 investments, I only want to send him 1 letter to his home address so I'm just trying to get rid of all the investment properties. The list I'm working with has thousands.

Thank you

 

[alansidman]

How are you removing the duplicates. Without knowing this, it is impossible to determine what is happening.

 

[tonynguyen10]

How are you removing the duplicates. Without knowing this, it is impossible to determine what is happening.

Hi, I am click on data > remove duplicates > select all > column D (there is only 1 row as an option).

When I complete this, the data is removed, but the rows don't follow so my data is incorrect now. Thank you for helping.

 

[alansidman]

Here is a macro vba solution that will do what you wish.

Option Explicit


Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Dim r As Long
Dim n As Long
Dim V As Variant
Dim rng As Range


On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual




Set rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))


Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")


n = 0
For r = rng.Rows.Count To 2 Step -1
If r Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
End If


V = rng.Cells(r, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
End If
Next r


EndMacro:


Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(n)




End Sub