compare 2 columns and list match

[tehabte]

I am working with excel 2010.
Here is an example of what I am trying to do

A column

Crisp prawn dumplings with green tea salt

Roast fennel, mint and ruby grapefruit salad

B column

Bacon

salad

mint

dumplings

tea

beef

RESULT C column

dumplings tea

mint salad

I am trying to search words that are in column B inside column A and return the matched results at C column. Thank you for Helping

 

[ERIM]

Here's some VBA code that will do do what you ask. Not very elegant I know, but it gives the answers expected.


Sub tehabte()
'
' Macro in answer to post on Mr Excel message board
'
'
Range("A1").Select
Selection.End(xlDown).Select
cola = ActiveCell.Row 'the number of rows of data in column A stored in 'cola'
Range("B1").Select
Selection.End(xlDown).Select
colb = ActiveCell.Row 'the number of rows of data in column B stored in 'colb'
For x = 1 To cola 'start of loop for column a
Range("A" & x).Select
stra = ActiveCell
colc = "" 'initialise variable
For n = 1 To colb 'start of loop for column B
Range("B" & n).Select
If InStr(1, stra, ActiveCell) > 0 Then 'INSTR will return the the start position of the string being searched for. If string not found returns 0.
colc = colc & " " & ActiveCell 'If found, store string in 'colc'
End If
Next n
Range("C" & x) = colc 'all rows in colb B processed, write stored strings in column C
Next x
End Sub

Please note that the data in columns A and B must be in contiguous cells (i.e. no blank cells).

 

[tehabte]

Thank you very much for a quick respond. It works very well but the only thing is that I want the code to pick up the first word from columnA.

Example

columnA columnB columnC

1 teaspoon ground allspice allspice allspice ground
ground


Can you help me so that columnC result is ground allspice instead of allspice ground.

Thanks again. your help means a lot.

 

[cyrilbrd]

Hi,
considering helper Columns and vba:

Crisp prawn dumplings with green tea salt	Bacon	dumplings, tea
Roast fennel, mint and ruby grapefruit salad	salad	mint, salad
                                         	mint	
                                        	dumplings	
                                           	tea	
                                           	beef

In Column A your menu
In Column B your dishes sorted alphabetically
In Column C type:
=IF(ISNUMBER(FIND(B1,$A$1)),B1, "")

In Column D type:
=IF(ISNUMBER(FIND(B1,$A$2)),B1, "")

Vba code:

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
    Dim Cell As Range, Area As Variant
    If IsMissing(Delimiter) Then Delimiter = ""
    For Each Area In CellRanges
        If TypeName(Area) = "Range" Then
            For Each Cell In Intersect(Area, Area.Parent.UsedRange)
                If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
            Next
        Else
            ConCat = ConCat & Delimiter & Area
        End If
    Next
    ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

In E1 type:
=ConCat(" ",C1:C6)

In E2 type:
=ConCat(" ",D1:D6)

You may hide Columns C and D.

For your perusal.

 

[Marcelo Branco]

Another one

Sub aTest()
    Dim s As Variant, i As Long, j As Long
    Dim strResult As String
    
    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        strResult = ""
        s = Split(Range("A" & i).Text, " ")
        For j = LBound(s) To UBound(s)
            If Application.CountIf(Range("B:B"), s(j)) Then _
                strResult = strResult & s(j) & ", "
        Next j
        If strResult <> "" Then strResult = Left(strResult, Len(strResult) - 2)
        Range("C" & i) = strResult
    Next i
        
End Sub

M.

 

[tehabte]

Thank you all,

They all work very well. I am using the last answer because it is a little simpler.