• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

Filter duplicates from Excel column selectively.

I have two columns in an Excel spreadsheet. For example

A                              B
A.txt                         3
B.txt                         1
B.txt                         1
B.txt                         12
C.txt                          3
D.txt                         2
E.txt                          34
E.txt                          34

What I need to do is (using VBA) delete any duplicate rows that exist in the A column, but at the same time keep the largest value related to the column. So the above would become...

A                              B
A.txt                         3
B.txt                         12
C.txt                          3
D.txt                         2
E.txt                          34

Any ideas?
0
Blowfelt82
Asked:
Blowfelt82
  • 4
  • 3
1 Solution
 
Ken ButtersCommented:
I recorded this macro...

first sort columns
Col A--- ascending
Col B -- Decending.

Second in data tab... click on remove duplicates button....
Identify Column A as the column to use for dup checks.  It will keep the first of each list... which because of the sort order is the highest.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B8"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B8")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Open in new window

0
 
Blowfelt82Author Commented:
It looks good, but I need a purely VBA solution so I cant use the remove duplicates button.
0
 
Ken ButtersCommented:
the code posted above as "Macro1" is purely vba code.... I just showed you how i produced it.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Blowfelt82Author Commented:
Ahh OK, sorry I misunderstood you... one thing though my data range is much greater than 8 rows, is there a way I can make this work on a random amount of rows rather than 'Range("A1:A8")' for example?
0
 
Blowfelt82Author Commented:
Also the top row is a header so I dont want this to be included in this process if possible?
0
 
Blowfelt82Author Commented:
Ok ignore the previous two comments! Is there any way though that I can implement the remove duplicates functionality via VBA (with any relevant configuration) without any user interaction?
0
 
Ken ButtersCommented:
instead of 8.... just make it large.... like 65000... will that work? and then since you have headers... start with row 2.

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A65000"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B65000"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:B65000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub 

Open in new window

0
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

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now