Solved

replace duplicate with blank record

Posted on 2010-11-08
2
467 Views
Last Modified: 2012-05-10
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
Comment
Question by:insp
2 Comments
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 34085546
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
 

Author Closing Comment

by:insp
ID: 34085609
Absolutely Awesome! - thanks!!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question