• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

replace duplicate with blank record

Hi, I need to remove duplicates in a given column, but leave the rest of the row intact., so if i have:
ColumnA   ColumnB   ColumnC
Sue              Apple        x
Sue              Apple        x
Sue              Pear          x
John             Apple        x
John             Apple        x
would get:
ColumnA   ColumnB   ColumnC
Sue                Apple        x
                                       x
                       Pear         x
John              Apple         x
                                        x

hope that makes sense, thanks!
0
insp
Asked:
insp
1 Solution
 
TracyVBA DeveloperCommented:
This will remove duplicate values but keep your rows intact.

Option Explicit

Sub ReplaceDuplicates()

    Dim i As Long
    Dim lastRow As Long
    Dim tempVal1 As String
    Dim tempVal2 As String
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("A1:C" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlYes

    For i = lastRow To 2 Step -1
        If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
            Cells(i, 1).Value = ""
            If Cells(i, 2).Value = Cells(i - 1, 2).Value Then
                Cells(i, 2).Value = ""
            Else
            End If
        Else
        End If
    Next i

End Sub

Open in new window

Book1.xls
0
 
inspAuthor Commented:
Absolutely Awesome! - thanks!!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now