Removing Duplicate Pairs(2 Columns)

  • Thread starter Thread starter deathswan
  • Start date Start date
D

deathswan

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. The 2 columns are right next to each
other (order_no is column C, item_no is column D).

Thanks!
[kyle]
 
Here is a solution that requires a helper column
and Advanced Filter.
Assume your data looks like this:
Order Item Bin
2 2 22
3 5 53
3 1 13
4 5 54
2 1 12
1 5 51
4 3 34
1 4 41
5 3 35
4 1 14

Criteria
Test
TRUE

order item
3 1
4 5
2 1
1 5
4 3

Bin is the helper column.
Select the 11x3 array that includes the headers
Order, Item and Bin.
Insert > Name > Create > Top Row
Do the same thing for the 3 cells that include Criteria.
Select the 11x2 array (leave out Bin) and
Insert > Name > Define > Name in Worksheet: Database
Fill the Bin column with this formula:
=10*Item+Order
Into the cell that says TRUE, enter this formula:
=NOT(COUNTIF(Bin,10*Order+Item))
Data > Filter > Advanced Filter > Copy to another location
List Range: Type in Database, or accept default
Criteria Range: Type in Criteria, or accept default
Copy To: Specify a location
If the numbers in Item and Order have more digits,
add a zero to the 10 for each addittional digit.
If the Item and Order numbers are very large,
use concatenate instead of multiply/add.
 
Without knowing more about your data (are there other columns besides C and D
to worry about, for instance), I would do this: Instead of deleting the
duplicates, I would extract the unique values.

Click a cell in your data. In the Data menu, point to Filter and click
Advanced Filter. If you clicked in the data first, the List Range should be
filled in already. Click the Copy to another location Option button and
select the Unique records only check box.
Click a cell to start in-- I used I1-- and click OK. Excel will extract
only the unique combinations.
 
I seem to have misread your question. Please disregard my previous post.
--
tj


tjtjjtjt said:
Without knowing more about your data (are there other columns besides C and D
to worry about, for instance), I would do this: Instead of deleting the
duplicates, I would extract the unique values.

Click a cell in your data. In the Data menu, point to Filter and click
Advanced Filter. If you clicked in the data first, the List Range should be
filled in already. Click the Copy to another location Option button and
select the Unique records only check box.
Click a cell to start in-- I used I1-- and click OK. Excel will extract
only the unique combinations.

--
tj


deathswan said:
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. The 2 columns are right next to each
other (order_no is column C, item_no is column D).

Thanks!
[kyle]
Click to expand...
Click to expand...
 
Back
Top