Hide rows that contain first two characters of a cell in adjacent cell

[kelly mort]

Hi,
I am challenged here and need a tech support team:

In column C I want to compare it's first two characters in each cell with the contents of cells in column D to see if those characters exist in the cell content then hide all rows that don't have a match.

So say I have "only" in C2 and "Money" in D2, the "on" can be found in "money" so the match is found. The characters in column D should follow each other. "on" <>"no"

For the same "only" from column C, if there is the word "column" in column D, there should be no match since the "o" and "n" are not consecutive.

Then move down to C3 and compare it's first two characters to D3 etc

I am looking for a macro to do that for me.

Thanks

 

[kelly mort]

The old data should always be overwritten

 

[Peter_SSs]

See how this goes.

Sub CopyIfFirstTwoMatch()
  Dim rCrit As Range
  
  Sheets("Sheet3").UsedRange.ClearContents
  With ActiveSheet.UsedRange
    Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
    rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),D2)"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("Sheet3").Range("A1"), Unique:=False
  End With
  rCrit.ClearContents
End Sub

 

[kelly mort]

See how this goes.

Sub CopyIfFirstTwoMatch()
  Dim rCrit As Range
  
  Sheets("Sheet3").UsedRange.ClearContents
  With ActiveSheet.UsedRange
    Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
    rCrit.Cells(2).Formula = "=SEARCH(TEXT(C2,""dd""),D2)"
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("Sheet3").Range("A1"), Unique:=False
  End With
  rCrit.ClearContents
End Sub

It went well - exactly what I wanted.
I am very grateful

 

[Peter_SSs]

It went well - exactly what I wanted.
I am very grateful

No problem - glad to help.