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]

This should do the trick

=IFERROR(INDEX($B$1:$B$3,MATCH(A1,$B$1:$B$3,0)),"")

Then copy the formula down for as many rows as you have information.

 

[lrobbo314]

Here is a VBA solution as well.

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("A1"), Range("A" & Rows.Count).End(xlUp))
Set r2 = Range(Range("b1"), Range("b" & Rows.Count).End(xlUp))
Dim iArray()
i = 1
For Each cel In r1
str = r2.Find(What:=cel, After:=r2.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
str = r2.Find(cel, 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

Anyone out there that sees a better way to do the VBA part, speak up. I'm sure there is a cleaner way to accomplish this task.

 

[Gain]

I have 92 rows in column A and 73821 rows in column B. What should the values be in formula?

 

[Gain]

This should do the trick

=IFERROR(INDEX($B$1:$B$3,MATCH(A1,$B$1:$B$3,0)),"")

Then copy the formula down for as many rows as you have information.

I have 92 rows in column A and 73821 rows in column B, what should the values be exactly in the formula? Thank you for this, it really is very helpful

 

[lrobbo314]

I know there is a better way to write that formula, but since you have such different sized columns I would use the VBA. But, if you want to use the formula, I would change it to

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

But if you want to use the VBA...

Hit Alt + F11
Alt + I, then M
Paste the code I posted earlier
Hit Alt + Q to return to Excel
Hit Alt + F8
Then run the macro

Both should work. Let me know if y have any issues.

 

[Gain]

I tried using the formula and nothing happens when I hit enter or click out of the cell.
I tried to run the VBA but the following error message appears:

"Run-time error '91':
Object variable or With block variable not set"
I have the option to "End", "Debug", or get "Help"

 

[Gain]

when I hit debug, the following line of code is highlighted:


str = r2.Find(What:=cel, After:=r2.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)

 

[lrobbo314]

I don't know why your having those problems. See, the test data below. I used the above solutions on this data and both worked. The only difference between the two is that the VBA solution will put everything t the top of Column C, whereas the formula solution will list the matches next to the value in column B as below.

Excel Workbook
	A	B	C
1	a	h	*
2	b	ri	*
3	c	d	*
4	*	e	*
5	*	w	*
6	*	s	*
7	*	d	*
8	*	e	*
9	*	ri	*
10	*	t	*
11	*	f	*
12	*	a	a
13	*	b	b
14	*	c	c
Sheet1

 

[Gain]

I followed your instructions for the vba and I continue to get the error message. I'm not familiar with excel, is there a simple setting or something that may cause this?
For the formula, I have a sheet open with only two columns of information. I select cell c1 and paste the formula, hit enter and nothing happens. Again, is there something simple that I'm missing? Thank you for your help, I really do appreciate it