Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro that analyses 2 columns . Delets row if same text is fo
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro that analyses 2 columns . Delets row if same text is fo
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 --------------- Jacob Skaria "andrei" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro that analyses 2 columns . Delets row if same text is fo
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro that analyses 2 columns . Delets row if same text i
Thanks guys for the help !
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro analyses cells in 2 columns . If same text found , keeps row | Excel Programming | |||
Macro analyses Cell . If text found , delets entire row | Excel Programming | |||
Macro that analyses data from 3 columns and puts result in 4th | Excel Programming | |||
Macro that delets all cells containg ALL words in bold | Excel Programming | |||
Macro which searches for a character . When found delets what's be | Excel Programming |