How can I remove duplicate rows and reversed-order duplicate rows in VBA-Excel

Question

-updated -

I wonder how to remove duplicate rows based on values in two columns, where duplicates can occur in reversed order too. I want to remove whole rows, not only duplicate cells in columns. I have searched the forum but cant find the exact answer to this.

If the data looks like:

AA
AB
BA
CA
AC

I want to keep:

AA
AB
CA

So far I have only seen code to remove one-way duplicates (e.g. Delete all duplicate rows Excel vba ), which would not remove any rows from the example above.

I will need to calculate the distance between two named points, so what row I keep does not matter. Maybe I should also specify that the format is e.g. A1 and A2 (letter + number) in each cell, thus A1+A2 and A2+A1 would be a duplicate. Not sure if this matters.

Does anyone know or can hint me in the right direction?

Thanks Lisarv

Do you always want to keep the first occurrence, or is there a different rule on which should be kept. — psubsee2003
– psubsee2003, Commented Jun 2, 2015 at 11:17

Gary's Student · Accepted Answer · 2015-06-02 12:57:17Z

5

Since you already have a solution based on a single column, we will find a way to apply that solution. With data in columns A and B, in C1 enter:

=IF(A1<B1,A1 & CHAR(1) & B1,B1 & CHAR(1) & A1)

and copy down. Then apply your solution to column C:

enter image description here

Note:

We use CHAR(1) to prevent accidental matches.

EDIT#1

Here is a pure VBA solution without using column C:

Sub RowKiller()
   Dim N As Long, C() As String, i As Long
   Dim aa As String, bb As String, cc As String
   Dim s As String
   s = Chr(1)
   N = Cells(Rows.Count, "A").End(xlUp).Row
   ReDim C(1 To N)

   For i = 1 To N
      aa = Cells(i, 1).Value
      bb = Cells(i, 2).Value
      If aa < bb Then
         C(i) = aa & s & bb
      Else
         C(i) = bb & s & aa
      End If
   Next i

   For i = N To 2 Step -1
      cc = C(i)
      For j = i - 1 To 1 Step -1
         If cc = C(j) Then Rows(i).Delete
      Next j
   Next i
End Sub

edited Jun 2, 2015 at 12:57

answered Jun 2, 2015 at 11:26

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lisarv Over a year ago

This was another way around it than I expected, thus I will keep the question open for a bit more. I was hoping for vba-code, as I will need to redo this and other operations a number of times and want to avoid manual steps. But, it does work, though by changing the commas to semi-colons! So indeed thanks for that @Gary

Gary's Student Over a year ago

@Lisarv I also will look for a solution without the "helper" column.

Collectives™ on Stack Overflow

How can I remove duplicate rows and reversed-order duplicate rows in VBA-Excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related