CODEWITHSUNDEEP
excelduplicatesvba
Ahmed Mohamed
Ahmed Mohamed

Reputation: 413

How to find corresponding matching pairs in two columns in Excel?

I am having 2 columns containing data in this format:

ColA ColB
a      x
b      y
c      z
x      a
y      b
z      c

I am having difficulty to filter out the matching pairs such as values a,x and x,a. Any help would be greatly appreciated!

EDIT:

Ideally I'd like to have a third colum with unique values to each pair such as

ColA ColB ColC
a      x     1
b      y     2
c      z     3
x      a     1
y      b     2
z      c     3

So that I can filter them out or do a pivot table quickly.

Upvotes: 0

Views: 6318

Answers (3)

Ahmed Mohamed
Ahmed Mohamed

Reputation: 413

Thank you all for your answers.

I just solved the problem using a simple semi-automatic way:

I first wrote a "match" in first cell in the third column (C2). Then I wrote this formula in the next cell (C3) based on an expanding VLOOKUP

=IF(ISNONTEXT(VLOOKUP(B3,$A$1:C2,3,0)),"match","")

I basically search for the current value in the second column, if its corresponding value in the first column has a match in the third column. If not then place "match"

That way I have "match" in the third column in front of unique values, which makes it very easy for me to manipulate further.

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Please give this a try...

Sub PairCount()
Dim x, y(), dict
Dim i As Long, cnt As Long
x = Range("A1").CurrentRegion.Value
ReDim y(1 To UBound(x, 1))
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    If Not dict.exists(x(i, 1) & x(i, 2)) Or Not dict.exists(x(i, 2) & x(i, 1)) Then
        cnt = cnt + 1
        dict.Item(x(i, 1) & x(i, 2)) = cnt
        dict.Item(x(i, 2) & x(i, 1)) = cnt
    End If
Next i
For i = 1 To UBound(x, 1)
    y(i) = dict.Item(x(i, 1) & x(i, 2))
Next i
Columns(3).Clear
Range("C1").Resize(UBound(y)).Value = Application.Transpose(y)
Set dict = Nothing
End Sub

Upvotes: 2

Variatus
Variatus

Reputation: 14383

How about using this kind of formula in the third column?

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

Upvotes: 1

Related Questions