Solved

replace duplicate with blank record

Posted on 2010-11-08
2
464 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
Comment Utility
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
Comment Utility
Absolutely Awesome! - thanks!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now