Duplicate removal rule extension needed

Posted on 2013-09-01
Medium Priority
Last Modified: 2013-09-17
I have the latest version of Excel as shown here http://screencast.com/t/TmhIc9kz.  If a duplicate company name occurs in column A, then the row that contains data in the least number of columns should be removed where the row that contains data in the most number of columns should remain.  Assistance is  greatly appreciated.
Question by:frugalmule
  • 3
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39458265
This isn't the complete answer, but it might get you started:


Author Comment

ID: 39458320
not sure where to go with that one?
LVL 52

Accepted Solution

Martin Liss earned 2000 total points
ID: 39458342
Here's a macro you can try.

Sub RemoveLeastData()
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim lngRow As Long
Dim FoundDupe As Range
Dim intCount As Integer
Dim intDupeCount As Integer

lngLastRow = ActiveSheet.UsedRange.Rows.Count
lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

Application.ScreenUpdating = False

For lngRow = lngLastRow - 1 To 2 Step -1
    Range(Cells(lngRow + 1, 1), Cells(lngLastRow, 1)).Select
    With Selection
        Set FoundDupe = .Find(What:=Cells(lngRow, 1), After:=ActiveCell, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    End With
    If Not FoundDupe Is Nothing Then
        intCount = Application.CountA(Range(Cells(lngRow, 1), Cells(lngRow, lngLastCol)))
        intDupeCount = Application.CountA(Range(Cells(FoundDupe.Row, 1), Cells(FoundDupe.Row, lngLastCol)))
        If intCount < intDupeCount Then
            Cells(lngRow, 1).EntireRow.Delete
            Cells(FoundDupe.Row, 1).EntireRow.Delete
        End If
    End If

Application.ScreenUpdating = True

End Sub

Open in new window

LVL 52

Expert Comment

by:Martin Liss
ID: 39468930
Did that help you?
LVL 52

Expert Comment

by:Martin Liss
ID: 39500865
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

607 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