Excel formula to return whats common in two text strings.

[Jamel]

Hello,

I'm looking for a way (excel formula) to compare two text strings and return what's in common between the two.

All the information is in one column so i will apply the formula in the column next to it and copy the formula down to the other rows.

[TABLE="width: 237"]
<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>[TR]
[TD="width: 237"]M T[/TD]
[/TR]
[TR]
[TD]M T W[/TD]
[/TR]
[TR]
[TD]T W H

The answer is T

Any help would be appreciated



[/TD]
[/TR]
</tbody>[/TABLE]

 

[Coding4Fun]

The question is not clear nor is the answer. You mention "I'm looking for a way (excel formula) to compare two text strings and return what's in common between the two." however you are saying T is the answer.

I see Row 1 and Row 2 have M & T in common. Row 2 and 3 have T & W in common.

Rows 1, 2 & 3 all have T in common. Looking to compare 2 or 3 text strings?

Also are you open to VBA or does it have to be a Formula?

 

[Jamel]

First and foremost. Thanks for your reply.

Now for the confusion. I was attempting to show the comparison of two cells and i also included the answer which is where the confusion kick in.

Im also often to vb but I'd thought it be too difficult because the source im using to apply the request formula points at a pivot table because the data is constantly changing. The pivot references 21,000 records. Rows of records. I knew the vb route would be more labor intensive so i was trying to not bother anyone that much.

 

[Peter_SSs]

I was attempting to show the comparison of two cells and i also included the answer which is where the confusion kick in.

To me, the confusion is not because you included the answer (blue) but because you say compare two cells when you have given three (red).

[TABLE="width: 237"]
<colgroup><col width="237" style="width:178pt"> </colgroup><tbody>[TR]
[TD="width: 237"]M T[/TD]
[/TR]
[TR]
[TD]M T W[/TD]
[/TR]
[TR]
[TD]T W H

The answer is T
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jamel]

I'm not sure how those colors were included.
I just typed the text. I didn't color code it. I see how that can be confusing

 

[Jamel]

Also what's obvious is the latest mistake on my part. I did mistakenly include three. My original question was comparing two. I Google it and couldn't find an existing formula that worked. And when i asked the question going a bit further. My mistake

 

[Jamel]

No suggestions?

 

[Coding4Fun]

Your question is still not clear but I think think is what you are looking for. Put your list in Column A, then put this code in a standard module and run it.

It will step through each Row of Column A and compare the Characters within each row to those in the next row. Each time it find a match it will output this in column B next to the "Top Row" in the comparison.

Example Table - Start in A, Resulting output in Column B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]M T[/TD]
[TD]M T[/TD]
[/TR]
[TR]
[TD]M T W[/TD]
[TD]T W[/TD]
[/TR]
[TR]
[TD]T W H[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]K A T[/TD]
[TD]K A T[/TD]
[/TR]
[TR]
[TD]A K T[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]T W H[/TD]
[TD]T W[/TD]
[/TR]
[TR]
[TD]W A P T[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub Test()


myRange = Cells(Rows.Count, "A").End(xlUp).Row


For e = 1 To myRange ' Loop through each Row


    For x = 1 To Len(Range("A" & e).Value) ' Loop through checking each Char vs Next Row
    
        checkVal1 = Mid(Range("A" & e).Value, x, 1)
    
        For y = 1 To Len(Range("A" & e + 1).Value) ' Loop through next Row Chars
        
            checkVal2 = Mid(Range("A" & e + 1).Value, y, 1)
            
            If checkVal1 = checkVal2 Then
            
            Range("B" & e).Value = Trim(Range("B" & e).Value) & " " & checkVal1


            End If
                
        Next y
    
    Next x


Next e


End Sub

 

[Coding4Fun]

Example Table - Start in A, Resulting output in Column B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]M T[/TD]
[TD]M T[/TD]
[/TR]
[TR]
[TD]M T W[/TD]
[TD]T W[/TD]
[/TR]
[TR]
[TD]T W H[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]K A T[/TD]
[TD]K A T[/TD]
[/TR]
[TR]
[TD]A K T[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]T W H[/TD]
[TD]T W[/TD]
[/TR]
[TR]
[TD]W A P T[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Also what's obvious is the latest mistake on my part. I did mistakenly include three.

That is what Coding4Fun & I were trying to point out all along.

I'm not sure how those colors were included.

I included the colour to try to highlight the above point.

I would suggest a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Common(s1 As String, s2 As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^" & Replace(s1, " ", "") & "]"
    Common = Application.Trim(.Replace(s2, " "))
  End With
End Function

I have assumed that your sample data is representative in that
- each string consists of single characters separated by a standard space character.
- there are no repeat characters in any of the strings **

Excel Workbook
	A	B
1	Data	Common
2	M T	M T
3	M T W	T W
4	T W H	T
5	K A T	A K T
6	A K T	T
7	T W H	W T
8	W A P T
Sheet2

** If my assumption about no repeat characters is not correct, then what should be the result for this pair of strings?
Q R Z R Q
Q Q R R

or this pair?
Z
Z W W Z