CODEWITHSUNDEEP
sqlexcelduplicatessas
Ken
Ken

Reputation: 893

Delete duplicates when the duplicates are not in the same column

Here is a sample of my data (n>3000) that ties two numbers together:

id  a       b
1   7028344 7181310
2   7030342 7030344
3   7030354 7030353
4   7030343 7030345
5   7030344 7030342
6   7030364 7008059
7   7030659 7066051
8   7030345 7030343
9   7031815 7045692
10  7032644 7102337

Now, the problem is that id=2 is a duplicate of id=5 and id=4 is a duplicate of id=8. So, when I tried to write if-then statements to map column a to column b, basically the numbers just get swapped. There are many cases like this in my full data.

So, my question is to identify the duplicate(s) and somehow delete one of the duplicates (either id=2 or id=5). And I preferably want to do this in Excel but I could work with SQL Server or SAS, too.

Thank you in advance. Please comment if my question is not clear.

What I want:

id  a       b
1   7028344 7181310
2   7030342 7030344
3   7030354 7030353
4   7030343 7030345
6   7030364 7008059
7   7030659 7066051
9   7031815 7045692
10  7032644 7102337

Upvotes: 0

Views: 439

Answers (2)

Joe
Joe

Reputation: 63424

All sorts of ways to do this.

In SAS or SQL, this is simple (for SQL Server, the SQL portion should be identical or nearly so):

data have;
input id a b;
datalines;
1   7028344 7181310
2   7030342 7030344
3   7030354 7030353
4   7030343 7030345
5   7030344 7030342
6   7030364 7008059
7   7030659 7066051
8   7030345 7030343
9   7031815 7045692
10  7032644 7102337
;;;;
run;

proc sql undopolicy=none;
delete from have H where exists (
  select 1 from have V where V.id < H.id
    and (V.a=H.a and V.b=H.b) or (V.a=H.b and V.b=H.a)
);
quit;

The excel solution would require creating an additional column I believe with the concatenation of the two strings, in order (any order will do) and then a lookup to see if that is the first row with that value or not. I don't think you can do it without creating an additional column (or using VBA, which if you can use that will have a fairly simple solution as well).

Edit: Actually, the excel solution IS possible without creating a new column (well, you need to put this formula somewhere, but without ANOTHER additional column).

=IF(OR(AND(COUNTIF(B$1:B1,B2),COUNTIF(C$1:C1,C2)),AND(COUNTIF(B$1:B1,C2),COUNTIF(C$1:C1,B2))),"DUPLICATE","")

Assuming ID is in A, B and C contain the values (and there is no header row). That formula goes in the second row (ie, B2/C2 values) and then is extended to further rows (so row 36 will have the arrays be B1:B35 and C1:C35 etc.). That puts DUPLICATE in the rows which are duplicates of something above and blank in rows that are unique.

Upvotes: 2

OneRealWinner
OneRealWinner

Reputation: 677

I haven't tested this out but here is some food for thought, you could join the table against itself and get the ID's that have duplicates

 SELECT
     id, a, b
 FROM
    [myTable]
    INNER JOIN ( SELECT id, a, b FROM [myTable] ) tbl2
        ON [myTable].a = [tbl2].b
           OR [myTable].b = tbl2.a

Upvotes: 0

Related Questions