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

 

[lrobbo314]

As far as the formula goes, you need to copy the formula down. Meaning that if you have 73821 rows of data in column B, the formula has to be copied down to that row in column C. The easiest way to do this is after you've entered the formula below into cell C1, go to the bottom right corner of cell C1 until the cursor changes to a cross-hair symbol, then double click.

=IFERROR(INDEX($A$1:$A$92,MATCH(B1,$A$1:$A$92,0)),"")

As you copy the formula down, the relative reference B1 will increase on its own up to 73821. When there is a match of one of the values in column B, the formula in column C will show up next to it. Otherwise it will be blank.

Again, I think the VBA is a better solution. If you want, you can send me a private message and I'll give you my email so you can send me a copy of your workbook so that I can see why you're getting an error and I can get it working.

 

[Gain]

The formula has returned the matches, thank you very much. If you don't mind, I will send you a message

 

[lrobbo314]

Glad that the formula is working. Yeah go for it, we'll get the vba working as well.

 

[lrobbo314]

I believe that this code will work now.

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, xlWhole)
        If Not Found Is Nothing Then
            str = r2.Find(cel, r2.Cells(1, 1), xlValues, xlWhole)
            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("C1"), Range("C" & WorksheetFunction.CountA(iArray())))
For i = 1 To WorksheetFunction.CountA(iArray())
    r3.Cells(i, 1).Value = iArray(i)
Next i

End Sub

 

[Gain]

It works now, thank you. How does it differ from the formula? I noticed that some of the common text was listed in the first few cells, but it doesn't list all of the common text at the top

 

[Gain]

I spoke too soon. It seems as though all of the common text is listed starting in the first row. Is this correct?

 

[lrobbo314]

Once you said that I went back to see what might be going on, and I was about to say "I don't see what you mean", but I noticed that I wasn't accounting for the headers in columns A and B. The following code has been tweaked to accommodate this.

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, xlWhole)
        If Not Found Is Nothing Then
            str = r2.Find(cel, r2.Cells(1, 1), xlValues, xlWhole)
            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" & WorksheetFunction.CountA(iArray())))
For i = 1 To WorksheetFunction.CountA(iArray())
    r3.Cells(i, 1).Value = iArray(i)
Next i

End Sub

 

[Gain]

Does it only return exact matches? In the file I sent you (in the first sheet), in cell A9 the cell has the following text: < bgcolor >. In cell B10, it has: < bgColor >00ffffff< /bgColor > . Is there anyway to find any text that is contained in other text as well as exact matches?

 

[lrobbo314]

By changing the Find argument from xlWhole to xlPart you can search within a string instead of an exact match as below...

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" & WorksheetFunction.CountA(iArray())))
For i = 1 To WorksheetFunction.CountA(iArray())
    r3.Cells(i, 1).Value = iArray(i)
Next i

End Sub

 

[Gain]

It seemed to work for the first sheet with fewer rows, but I ran it on the second sheet with 73,000+ rows and it gave me this error:
"Runtime error '13'
Type Mismatch". Is this an error in the code or my data?