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

Raw Data</SPAN>
Group</SPAN>	Sec Code</SPAN>	Item Name</SPAN>	Purchase</SPAN>	Pay Date</SPAN>	Rate</SPAN>
12345</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	08-Jan-14</SPAN>	03-Feb-14</SPAN>	0.45</SPAN>
12345</SPAN>	123456789</SPAN>	Item 2</SPAN>	13-Jan-14</SPAN>	17-Feb-14</SPAN>	0.23</SPAN>
ABCD</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	08-Jan-14</SPAN>	03-Feb-14</SPAN>	0.45</SPAN>
ABCD</SPAN>	035710411</SPAN>	Item 3</SPAN>	27-Dec-13</SPAN>	31-Jan-14</SPAN>	0.3</SPAN>
ABCD</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	27-Dec-13</SPAN>	31-Jan-14</SPAN>	0.4</SPAN>
6789</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	08-Jan-14</SPAN>	03-Feb-14</SPAN>	0.48</SPAN>
6789</SPAN>	123456789</SPAN>	Item 2</SPAN>	13-Jan-14</SPAN>	17-Feb-14</SPAN>	0.23</SPAN>
6789</SPAN>	00287Y109</SPAN>	Item 4</SPAN>	13-Jan-14</SPAN>	14-Feb-14</SPAN>	0.4</SPAN>
Desired Results</SPAN>
Group</SPAN>	Sec Code</SPAN>	Item Name</SPAN>	Purchase</SPAN>	Pay Date</SPAN>	Rate</SPAN>
12345</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	08-Jan-14</SPAN>	03-Feb-14</SPAN>	0.45</SPAN>
12345</SPAN>	123456789</SPAN>	Item 2</SPAN>	13-Jan-14</SPAN>	17-Feb-14</SPAN>	0.23</SPAN>
ABCD</SPAN>	035710411</SPAN>	Item 3</SPAN>	27-Dec-13</SPAN>	31-Jan-14</SPAN>	0.3</SPAN>
ABCD</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	27-Dec-13</SPAN>	31-Jan-14</SPAN>	0.4</SPAN>
6789</SPAN>	11396Q102</SPAN>	Item 1</SPAN>	08-Jan-14</SPAN>	03-Feb-14</SPAN>	0.48</SPAN>
6789</SPAN>	00287Y109</SPAN>	Item 4</SPAN>	13-Jan-14</SPAN>	14-Feb-14</SPAN>	0.4</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=2><COL></COLGROUP>

 

[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