Finding cells with at least the same text present in other cells (Excel)

Question

I have two columns into two separate sheets. In the first, C1, I have 1000 rows containing text, in the second column C2 I have 2000 rows also containing text.

What I need to do is to select all the C2 rows which contain at least (exactly) all the text contained in any of the C1 rows.

For instance, suppose data are like this:

    C1         C2
Eval 1      Eval 101 doc
Eval 1      Eval 1 project
Eval 100    Eval 1 doc
Ev 1        Eval 19
Eval1       Ev 2

In the end I would like to select only the following cells in the second sheet:

    C2
Eval 1 doc
Eval 1 project

I am not very familiar with Excel, but I produced the following function:

=IF(ISNUMBER(FIND(<C1 cell>, <C2 cell>)),"OK", "Not OK")

and then it should just be a matter of filtering data according to "OK".

The problem is that the function works only for single C1 values, but not if I select the entire C1 column as I need to..

I also thought to use VLOOKUP, but then what I need is not the C2 values perfectly matching with C1 values but, as I said before, the ones that at least contain the entire text of the C1 cells..

Any suggestion would be highly appreciated,

Stefano

You might want to try VBA [regex](http://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function) — zx8754, Nov 08 '13 at 13:50

score 1 · Accepted Answer · edited Nov 13 '13 at 09:06

Ok, try this, it's not great, but I think it will work for you:

Add the following code to your workbook in a standard module, and save the workbook

Public LookInCollection As New Collection

Function SetUpCollection(range)
'Load data into collection
    On Error Resume Next
    For Each Cell In range
        LookInCollection.Add 1, Cell.Text
    Next
    On Error GoTo 0
End Function

Function MatchValue(Value)
    Dim ValueArray As Variant
    Dim Lenght As Long

    ValueArray = Split(Value, " ")

    'Loop String backwars remving words each time
    For i = 0 To UBound(ValueArray)
        If InCollection(Left(Value, Len(Value) - Lenght)) Then
            MatchValue = 1
            Exit Function
        End If
        Lenght = Len(ValueArray(UBound(ValueArray) - i)) + 1
    Next

    MatchValue = 0    'Cound not match
End Function

Private Function InCollection(Key) As Variant
    On Error Resume Next
    InCollection = LookInCollection(Key)
End Function

Next on the page with column 2 on it add one formula like this

=SetUpCollection(Column1_Range)

then add the forumla next to each cell in C2,

=MatchValue(ReftoCelltoCheck)

Update based on comment

The code has 3 parts. The first function builds a collection, that is used by the main function, that's why it's only used once. The last function just checks to see if the key is in the collection.

The second function does the work. It gets the value to check and loads each word in to an array, this is the split function. The for loop works backwards thought the value, removing words from the original value and checking this new value against the keys in the collection. A match returns 1 and we exit the function, else keep going until all the words are checked.

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. — Mureinik, Nov 08 '13 at 14:33
@Mureuink - Yeah, sorry, I messed up, I have given a decent(ish) answer now — Ross, Nov 08 '13 at 14:37
much better, thanks. Moderators - I couldn't find a way to remove my flag, please ignore it. — Mureinik, Nov 08 '13 at 14:39
Works as intended! Thank you very much! If you or other users have time and will, could you please give me an idea in words of what the macro is doing? — Stefano Lombardi, Nov 08 '13 at 15:45
Glad it worked. The code has 3 parts. The first function builds a collection The last function just checks to see if the key is in the collection. The second function does the work. It gets the value to check and loads each word in to an array, this is the split function. The for loop works backwards thought the value, removing words from the original value and checking this new value against the keys in the collection. A match returns 1 and we exit the function, else keep going until all the words are checked. Hope this makes sense and is readable! Ross — Ross, Nov 15 '13 at 16:23

Finding cells with at least the same text present in other cells (Excel)

1 Answers1