Need macro that analyses 2 columns . Delets row if same text is fo

[andrei]

A little bit tricky . Have column B with cells containing text . Have D
column with cells containing text . Example :

B1 : Mother D1 : Son and mother are going home
B2 : Father D2 : Rain in London
B3 : John and his uncle D3 : Some time ago John and his uncle did...
B4 : Mother and father D4 : Mother cooks great
B5 : Mother and son D5 : Mother and father and son

The macro should look in every cell in B column . If same text is found in
cell from D column , deletes the entire row . Else , keeps the row
Capitalisation should not matter

"Mother" from B1 is found in D1 . So delets the row
"Father" from B2 not to be found in D2 . Keeps the row
"John and his uncle" from B3 found in D3 . Delets the row
"Mother and father" from B4 not found in D4 . Keeps the row . Even though
"Mother" is found in D4 , not all words from B4 can be found in D4 (ex :
father)
"Mother and son" from B5 can be found in D5 , but not in the order they are
in B5 . So the macro keeps the row

I think i covered all possible cases

 

[Jacob Skaria]

Hi Andrei; try the below macro which works on the activesheet

Sub MyMacro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If InStr(1, Range("D" & lngRow), Range("B" & lngRow), _
vbTextCompare) Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[Sam Wilson]

This should give you the right idea...

Sub test()

Dim bMatch As Boolean

Dim aStr() As String
aStr = Split(Range("B1"), " ")

Dim i As Integer
For i = 0 To UBound(aStr)
If InStr(Range("D1").Value, aStr(i)) = 0 Then
bMatch = False
Exit For
End If
Next i

If bMatch = True Then MsgBox "All matched"

End Sub

 

[andrei]

Thanks guys for the help !