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]

It turns to the date format automatically

Yes that are all dates

After I enter the 04*12*04, it turns to '04*12*04 in the cell

 

[Peter_SSs]

Yes that are all dates

If column C are all dates then try this, assuming that those dates are in dd-mm-yy format (so 04-12-04 is 4 December 2004)
- Have the formatting as it used to be so that the cells shows the date formatted as you want (04-12-04)
- Try this code

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

If your dates are actually mm-dd-yy (so 04-12-04 is 12 April 2004) then the formula to try would be
=SEARCH(TEXT(C2,""mm""),D2)

 

[kelly mort]

Great!!!!


Problem solved

How do I show all columns back?

Regards
Kelly

 

[Peter_SSs]

How do I show all columns back?

Two choices:
a) Manually: Data ribbon tab - Clear (in the 'Sort & Filter' group)
b) Another code:

Sub Unhide_All()
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub

 

[kelly mort]

Thanks

 

[Peter_SSs]

Thanks

You're welcome

 

[kelly mort]

Hi,

Can the match rows be copied to a new sheet instead of hiding them?

If yes then I will be glad you show me how to do that.

Please let me know if I need a new thread for this

 

[Peter_SSs]

Can the match rows be copied to a new sheet instead of hiding them?

Can you clarify? Currently (post 12 code) the "match rows" are not hidden, it is the unmatched rows that are hidden.

 

[kelly mort]

Yes we hid the unmatched rows to show the matched rows.

So now intead of hiding the unmatched rows to show the matched rows, just copy the matched rows to a new sheet. Say sheet3 .

That sheet will always be available and not change.

 

[Peter_SSs]

Yes we hid the unmatched rows to show the matched rows.

So now intead of hiding the unmatched rows to show the matched rows, just copy the matched rows to a new sheet. Say sheet3 .

That sheet will always be available and not change.

OK, so "Sheet3" already exists and the matched rows get copied there. That much is clear.

What happens when/if the macro is run a 2nd, 3rd etc time? Should any data that is already on Sheet3 be removed before the new data is copied to it or should the new data be added to the existing data somehow? (How/where?)