Solved

replace duplicate with blank record

Posted on 2010-11-08
2
465 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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