Filter with multiple values contains

[Niranjanrajrishi]

Hello,

I am trying to create a code wherein the N column needs to be filtered with the cell values starting with "O", "S", "L", "M", "N". I tried with ARRAY but it does not work if the items are more than 2. Can you help me here to find the correct code?

Regards,
Niranjan

 

[vletm]

Niranjanrajrishi
If You would be look a VBA solution then ... split could help You.

 

[Niranjanrajrishi]

When you say SPLIT, use same coding for 3 times (1 code for first 2 letters, then 2nd one and lastly one letter)?

 

[vletm]

Niranjanrajrishi
Your original dream was: ... needs to be filtered with the cell values starting with "O", "S", "L", "M", "N" ...
... and now, Your writings gives image of something different ( eg "OO*", "?O*", "*O" ),
there seems to be unlimited versions or how?
That could soon mean like "*"!
There are possibilities,
but You should know,
what do You would like to do?
and
How? with formulas (as You've written this) or other way.

 

[Niranjanrajrishi]

Hey, thanks for the question.
I am trying to filter the text which starts with O, S, L, M, N. Does it help?

 

[vletm]

Niranjanrajrishi
I did two questions.
... and without answers, maybe someone else would start to guess.

... or as You should want to have a formula solution then ...
1) write eg to cell AAA1 text OSLMN
2) copy&paste below formula eg to cell AAA2
=IF(N2<>"",IFERROR(FIND(LEFT(N2,1),AAA$1,1),"no"),"-")
3) Copy down as needed
4) use normal filter functionality to get Your needed result

 

[Fluff13]

How about

Sub Niranjanrajrishi()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub

 

[Niranjanrajrishi]

Hey,

How about

Sub Niranjanrajrishi()
   Dim Cl As Range
 
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(Cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(Cl.Value) = Empty
         End Select
      Next Cl
      Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub

Hey,

This code works perfectly when the data exists. After filter, if there is no data then it shows error at "Range("N:N").AutoFilter 1, .Keys, xlFilterValues".

 

[Fluff13]

In that case, how about

Sub Niranjanrajrishi()
   Dim cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(cl.Value) = Empty
         End Select
      Next cl
      If .Count > 0 Then Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub

 

[Niranjanrajrishi]

In that case, how about

Sub Niranjanrajrishi()
   Dim cl As Range
 
   With CreateObject("scripting.dictionary")
      For Each cl In Range("N2", Range("N" & Rows.Count).End(xlUp))
         Select Case Left(cl, 1)
            Case "O", "S", "L", "M", "N"
               .Item(cl.Value) = Empty
         End Select
      Next cl
      If .Count > 0 Then Range("N:N").AutoFilter 1, .Keys, xlFilterValues
   End With
End Sub

Thank you. It worked!

 

[Fluff13]

You're welcome & thanks for the feedback