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

 

[Peter_SSs]

If C2 = "Only" and D2= "Money", is that still a match or not?

Are there headings in row 1?

 

[kelly mort]

Yes that's a match and yes there are headings in row 1

 

[Peter_SSs]

Then assuming ..
- Data starts in column A
- Column Z is unused
.. try this with a copy of your workbook.

Sub HideIfMatch2()
  Dim rCrit As Range
  
  Set rCrit = Range("Z1:Z2")
  rCrit.Cells(2, 1).Formula = "=SEARCH(LEFT(C2,2),D2)"
  Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  rCrit.Cells(2, 1).ClearContents
End Sub

 

[kelly mort]

The code is hiding all rows.

What should I do with the column Z?

When I change .Cells (2,1) to .cells (2,2), it unhides them all back.


PS.

Okay, I think i tested it on the wrong data type.

I used numbers for the testing when I changed to text, it worked.

Is there a way to make it do for numbers too?

Eg look for the first two characters of "04-12-04" in "00-01-02-03-04"?

 

[Peter_SSs]

The code is hiding all rows.

It isn't for me. So can you post a small set of dummy data, making sure I can identify what columns/rows it is in?

What should I do with the column Z?

Nothing

 

[kelly mort]

The code is hiding all rows.

What should I do with the column Z?

When I change .Cells (2,1) to .cells (2,2), it unhides them all back.


PS.

Okay, I think i tested it on the wrong data type.

I used numbers for the testing when I changed to text, it worked.

Is there a way to make it do for numbers too?

Eg look for the first two characters of "04-12-04" in "00-01-02-03-04"?

I
I am on phone which makes it tough for me to post a sample here.

Hope this clarifies it properly.

Regards
Kelly

 

[kelly mort]

Why does it not work for the numbers?

 

[kelly mort]

I have seen what's causing the problem :

It's treating the data "04-12-04" as date which becomes a different number which can't be located in column D. So I tried something like "04*12*04" and used

search (mid(C2, 2,2),D2 )

It's working cool I wish there is a way to prevent that date conversion

 

[Peter_SSs]

How is column C formatted?

Are all the values in column C "dates" or look like dates, or just some of them?

Why use search (mid(C2, 2,2),D2 )?
With your example of "04*12*04" that would be searching for "4*", not "04"