Remove Duplicate Pairs(2 Columns)

Hello. I have 2 columns order_no and item_no. Duplicates are allowed in each column separately, however I would like to remove all duplicate "pairs" of order_no and item_no. So for instance (order_no, item_no) -> [(1, 4) and (1, 4)] would be deleted, but [(1, 3) and (1, 4)] OR [(1, 3) and (2, 3)] would be be allowed. Basically my primary key is the superkey of both order_no and item_no which is why I can't have duplicates. The 2 columns are right next to each other (order_no is column C, item_no is column D).

Thanks!
[kyle]

You could use the concatinate function, and then sort on that column

order_no item_no Concatinate
4 1 41
4 1 41
4 2 42

Then use the code below (though all the words below are NOT keywords, some
are conceptual only to give you the idea):

For r = RowCount To 1 Step -1
Cells(r, 3).Select
celCompare = ActiveCell
If Range.Offset(-1, 0) = celCompare Then
'Delete current row
End If
Next r

Let me know if this helps

"deathswan" wrote:

>
> Hello. I have 2 columns order_no and item_no. Duplicates are allowed in
> each column separately, however I would like to remove all duplicate
> "pairs" of order_no and item_no. So for instance (order_no, item_no) ->
> [(1, 4) and (1, 4)] would be deleted, but [(1, 3) and (1, 4)] OR [(1, 3)
> and (2, 3)] would be be allowed. Basically my primary key is the
> superkey of both order_no and item_no which is why I can't have
> duplicates. The 2 columns are right next to each other (order_no is
> column C, item_no is column D).
>
> Thanks!
> [kyle]
>
>
> --
> deathswan
> ------------------------------------------------------------------------
> deathswan's Profile: http://www.excelforum.com/member.php...o&userid=33060
> View this thread: http://www.excelforum.com/showthread...hreadid=528737
>
>

Hi,
Try this code. It assumes that there are only four columns, A-D, and that
there are no column headers. If there are more columns, change the code in
the row that begins with "Range("A1:D....." to "Range("A1:E....".

Sub DeleteDupes()
Dim Iloop As Integer
Dim Numrows As Integer

'Turn off warnings, etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Numrows = Range("C65536").End(xlUp).Row
Range("A1:D" & Numrows).Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, _
Key2:=Range("D1"), Order2:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "C") & Cells(Iloop, "D") = Cells(Iloop - 1, "C") & _
Cells(Iloop - 1, "D") Then
Rows(Iloop).Delete
End If
Next Iloop

Range("A1").Select

'Turn on warnings, etc.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

--
Ken Hudson


"deathswan" wrote:

>
> Hello. I have 2 columns order_no and item_no. Duplicates are allowed in
> each column separately, however I would like to remove all duplicate
> "pairs" of order_no and item_no. So for instance (order_no, item_no) ->
> [(1, 4) and (1, 4)] would be deleted, but [(1, 3) and (1, 4)] OR [(1, 3)
> and (2, 3)] would be be allowed. Basically my primary key is the
> superkey of both order_no and item_no which is why I can't have
> duplicates. The 2 columns are right next to each other (order_no is
> column C, item_no is column D).
>
> Thanks!
> [kyle]
>
>
> --
> deathswan
> ------------------------------------------------------------------------
> deathswan's Profile: http://www.excelforum.com/member.php...o&userid=33060
> View this thread: http://www.excelforum.com/showthread...hreadid=528737
>
>