• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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