Cross-reference two columns with text and return common text in third column

[Gain]

Hi,

I have two columns with text in the cells on the same sheet (Columns A and B in my example below). Some words appear in both columns and I need to find out which words are common between them. I don't need to know how many times they appear in both columns. How can I do this in excel? If it is not possible to be done in excel, what other software can I use to return this information?


Here's an example of what I'd like to be done, with the common text to appear in Column C (or wherever, I just need to know what text is common):

Column A
address
create
color

Column B
table
pair
address

Column C
address

In reality, some of the columns I'd like to look through have 70,000+ rows.


Thank you

 

[Gain]

I debugged it and it highlighted this line of code:
Set r3 = Range(Range("C2"), Range("C" & WorksheetFunction.CountA(iArray())))

 

[lrobbo314]

This should take care of that. Still not sure why it was running into that error though.

Sub matches()
On Error GoTo n
Dim r1 As Range, r2 As Range, r3 As Range, cel As Range
Dim str As String
Set r1 = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Set r2 = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Dim iArray()
i = 1
For Each cel In r1
str = r2.Find(What:=cel.Text, After:=r2.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
str = r2.Find(cel.Text, r2.Cells(1, 1), xlValues, xlWhole)
    If Not IsNull(str) Then
        ReDim Preserve iArray(1 To i)
        iArray(i) = str
        i = i + 1
    End If
n:
str = vbNullString
Next cel

Set r3 = Range(Range("C1"), Range("C" & WorksheetFunction.CountA(iArray())))
For i = 1 To WorksheetFunction.CountA(iArray())
    r3.Cells(i, 1).Value = iArray(i)
Next i
End Sub

Sub matchx()

Dim r1 As Range, r2 As Range, r3 As Range, cel As Range, Found As Range
Dim str As String
Set r1 = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Set r2 = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Dim iArray()
i = 1

For Each cel In r1
    Set Found = r2.Find(cel, r2.Cells(1, 1), xlValues, xlPart)
        If Not Found Is Nothing Then
            str = r2.Find(cel, r2.Cells(1, 1), xlValues, xlPart)
            ReDim Preserve iArray(1 To i)
            iArray(i) = str
            str = vbNullString
            Set Found = Nothing
            i = i + 1
        End If
Next cel

Set r3 = Range(Range("C2"), Range("C" & UBound(iArray())))
For i = 1 To UBound(iArray())
    r3.Cells(i, 1).Value = iArray(i)
Next i

End Sub

 

[Gain]

When I run the matches code (the one you just posted), I get the error message I got in the first place: "Runtime error '91'
Object variable or With block variable not set"

 

[Gain]

When I'm prompted to run the code, I have to choose either 'matches' or 'matchx'. which one do I choose?

 

[lrobbo314]

matchx

 

[Gain]

It seems to be working
Just for the future, why matchx instead of matches?

 

[lrobbo314]

Matches was what I named the first one we tried. Matchx was version 2.0 so to speak.

Glad it's working for ya!