Solved

replace duplicate with blank record

Posted on 2010-11-08
2
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 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