VBA Multiple Critea Delete Duplicate Values

[JaysVBA]

Hello All,

I just took my first VBA course and brand new to the forum. I was hoping for some help in deleting duplicate values based off of multiple criterea. Each item has a unique Sec Code. I do not care about the "Group column." My desired results will show the security codes with all of the unique rates. Item 1 has 3 different rates (text color red in the desired results). So ideally, I would like to delete duplicates on items that have the same sec code and rate.

Any help would be greatly appreciated.
- JaysVBA

[TABLE="width: 767"]
<TBODY>[TR]
[TD="colspan: 6"]Raw Data</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]Sec Code</SPAN>[/TD]
[TD]Item Name</SPAN>[/TD]
[TD]Purchase</SPAN>[/TD]
[TD] Pay Date</SPAN>[/TD]
[TD]Rate</SPAN>[/TD]
[/TR]
[TR]
[TD]12345</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]08-Jan-14</SPAN>[/TD]
[TD]03-Feb-14</SPAN>[/TD]
[TD="align: right"]0.45</SPAN>[/TD]
[/TR]
[TR]
[TD]12345</SPAN>[/TD]
[TD]123456789</SPAN>[/TD]
[TD]Item 2</SPAN>[/TD]
[TD]13-Jan-14</SPAN>[/TD]
[TD]17-Feb-14</SPAN>[/TD]
[TD="align: right"]0.23</SPAN>[/TD]
[/TR]
[TR]
[TD]ABCD</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]08-Jan-14</SPAN>[/TD]
[TD]03-Feb-14</SPAN>[/TD]
[TD="align: right"]0.45</SPAN>[/TD]
[/TR]
[TR]
[TD]ABCD</SPAN>[/TD]
[TD]035710411</SPAN>[/TD]
[TD]Item 3</SPAN>[/TD]
[TD]27-Dec-13</SPAN>[/TD]
[TD]31-Jan-14</SPAN>[/TD]
[TD="align: right"]0.3</SPAN>[/TD]
[/TR]
[TR]
[TD]ABCD</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]27-Dec-13</SPAN>[/TD]
[TD]31-Jan-14</SPAN>[/TD]
[TD="align: right"]0.4</SPAN>[/TD]
[/TR]
[TR]
[TD]6789</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]08-Jan-14</SPAN>[/TD]
[TD]03-Feb-14</SPAN>[/TD]
[TD="align: right"]0.48</SPAN>[/TD]
[/TR]
[TR]
[TD]6789</SPAN>[/TD]
[TD]123456789</SPAN>[/TD]
[TD]Item 2</SPAN>[/TD]
[TD]13-Jan-14</SPAN>[/TD]
[TD]17-Feb-14</SPAN>[/TD]
[TD="align: right"]0.23</SPAN>[/TD]
[/TR]
[TR]
[TD]6789</SPAN>[/TD]
[TD]00287Y109</SPAN>[/TD]
[TD]Item 4</SPAN>[/TD]
[TD]13-Jan-14</SPAN>[/TD]
[TD]14-Feb-14</SPAN>[/TD]
[TD="align: right"]0.4</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Desired Results</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]Sec Code</SPAN>[/TD]
[TD]Item Name</SPAN>[/TD]
[TD]Purchase</SPAN>[/TD]
[TD] Pay Date</SPAN>[/TD]
[TD]Rate</SPAN>[/TD]
[/TR]
[TR]
[TD]12345</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]08-Jan-14</SPAN>[/TD]
[TD]03-Feb-14</SPAN>[/TD]
[TD="align: right"]0.45</SPAN>[/TD]
[/TR]
[TR]
[TD]12345</SPAN>[/TD]
[TD]123456789</SPAN>[/TD]
[TD]Item 2</SPAN>[/TD]
[TD]13-Jan-14</SPAN>[/TD]
[TD]17-Feb-14</SPAN>[/TD]
[TD="align: right"]0.23</SPAN>[/TD]
[/TR]
[TR]
[TD]ABCD</SPAN>[/TD]
[TD]035710411</SPAN>[/TD]
[TD]Item 3</SPAN>[/TD]
[TD]27-Dec-13</SPAN>[/TD]
[TD]31-Jan-14</SPAN>[/TD]
[TD="align: right"]0.3</SPAN>[/TD]
[/TR]
[TR]
[TD]ABCD</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]27-Dec-13</SPAN>[/TD]
[TD]31-Jan-14</SPAN>[/TD]
[TD="align: right"]0.4</SPAN>[/TD]
[/TR]
[TR]
[TD]6789</SPAN>[/TD]
[TD]11396Q102</SPAN>[/TD]
[TD]Item 1</SPAN>[/TD]
[TD]08-Jan-14</SPAN>[/TD]
[TD]03-Feb-14</SPAN>[/TD]
[TD="align: right"]0.48</SPAN>[/TD]
[/TR]
[TR]
[TD]6789</SPAN>[/TD]
[TD]00287Y109</SPAN>[/TD]
[TD]Item 4</SPAN>[/TD]
[TD]13-Jan-14</SPAN>[/TD]
[TD]14-Feb-14</SPAN>[/TD]
[TD="align: right"]0.4</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=2><COL></COLGROUP>[/TABLE]

 

[bzelenak]

Maybe add a column (lets call it column X) in which you concatenate Sec Code and Rate, and then remove duplicates. Then delete column X.

 

[bzelenak]

If all you care about is Sec Code and Rate, then maybe insert a new column (lets call it column X) in which you concatenate the fields you care about (Sec Code and Rate). Then remove duplicates and delete column X.

 

[JaysVBA]

Thank you. I like the concatenate idea but I would like this to be a part of a larger macro. I assume I could just record such function but is there a simpler way using vba?

 

[mirabeau]

Thank you. I like the concatenate idea but I would like this to be a part of a larger macro. I assume I could just record such function but is there a simpler way using vba?

Sub dxxd()

Dim d As Object, a, u, i As Long, j As Long
Set d = CreateObject("scripting.dictionary")

With Range("A1").CurrentRegion.Resize(, 6)
    a = .Value
    For i = 2 To .Rows.Count
        u = a(i, 2) & Chr(30) & a(i, 6)
        If Not d.exists(u) Then
            d.Add u, Nothing
            For j = 1 To 6
                a(d.Count + 1, j) = a(i, j)
            Next j
        End If
    Next i
    .ClearContents
    .Resize(d.Count + 1, 6) = a
End With

End Sub

There is a RemoveDuplicates facility in Excell 2007 and on that should do the same when it works, but it may have a bug. Check google on that one.

 

[C Moore]

click a cell in your data

Sub remove_dupes
ActiveCell.CurrentRegion.RemoveDuplicates Columns:=Array(2, 6), Header:=xlYes
End Sub