Filter multiple columns for a single word

[oceanorigami]

I want to run a filter "contains" RND on multiple columns but can't figure out how to do it. I think I need to use a macro or the Advanced Filter. Can anyone help explain how to do it?

 

[Peter_SSs]

Welcome to the MrExcel board!

Advanced Filter should achieve what you are after.

1. Are you looking for a macro or manual approach?

2. Where is your data? That is, what rows/columns does it occupy?

3. Does it have headings? What row are the headings in?

4. What columns do you want to check for "RND"?

5. Can you confirm that you want to show only rows where at least one of the columns of interest contains "RND"?

6. What version of Excel are you using?

 

[oceanorigami]

1. I'm looking for the easiest approach but a macro would be welcome, however I'm very new and don't understand how to actually use a macro in a spreadsheet. I found out I can press ALT and F11 and bring some weird interface up for Visual Basic but that's all I know.

2. My data in around 10k rows, each row has columns for name, company, etc. What I'm trying to do is filter it so it will only show me rows containing the term RND. I can do it with 1 column at a time but there are like 20 others and that is impossible to do manually.

3. It does have headings yes, what row? Row 1

4. I want to check the columns I tell it to check, Text1, Text2, Text3, etc. there are 20 columns like that! I can't do it manually I'd go insane.

5. I can confirm I want to show only columns containing the word "RND" but they also have other text like "Made for RND per" so it can't just look solely for RND it has to show results containing the word.

6. I am using 2007

I tried using the Advanced Filter and setting up a filter using *RND* but it does a cascading filter just like if I were to do manually in each column. If I select a column and custom filter than ask it to look for anything containing RND it works, but then on the next column it doesn't because it already filtered the first column which ignores any further columns with RND. It's so annoying!

Anyways if you can help me I will be grateful

Example: I just want to be able to have the spreadsheet show me whatever rows will have RND. I need to run something and it will ignore any rows without RND in them. I guess I'm confused because manually doing it I select the column but yea.

Name	Company	Notes	Notes2	Notes3
Evans, Sara	Marco Polo	Sold	Bought	RND
Lost, Marcus	Deadbeat	Bought	RND	Sold
Cyrus, Bill	Linksbeat	Sold	Bought	RND
Marshall, Becky	Hero	RND	Sold	Bought
Miller, Dennis	Letgo	Sold	Bought	Sold
Link, Karen	Greatso	Bought	Sold	Bought
Like, Link	Marshmellow	Sold	Bought	Sold

<tbody>
</tbody>

 

[Peter_SSs]

I want to check the columns I tell it to check, Text1, Text2, Text3, etc. there are 20 columns like that

OK, the columns are not really clear to me. From your sample data I have assumed you want to check from column C to the last column in your data and only display rows that have "RND" somewhere in those columns. If that is not what you want, please advise exactly which column letters need to be checked.

To implement ..

1. With your worksheet active, Alt+F11 to open the Visual Basic window

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Your workbook will need to be saved as a macro-enabled file (*.xlsm)

6. From the workbook press Alt+F8 to open the Macro dialog (or View tab -> Macros or Developer tab (if you have it) Macros) -> Macros and select Showq_My_Rows_of_Interset then press 'Run'

Sub Show_My_Rows_of_Interest()
  Dim rCrit As Range
  Dim sFormula As String, sRng As String
 
  Const TextOfInterest As String = "RND"
  Const FirstColOfInterest As String = "C"
  Const fBase As String = "=COUNTIF(#,""*%*"")"
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  With ActiveSheet
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    With .UsedRange
      sRng = FirstColOfInterest & "2:" & .Cells(2, .Columns.Count).Address(0, 0)
      Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
      rCrit.Cells(2).Formula = Replace(Replace(fBase, "#", sRng, 1, -1, 1), "%", TextOfInterest, 1, -1, 1)
      .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.ClearContents
  End With
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
 
End Sub

To show all the rows again, either ..

a) Data ribbon tab|Clear (in the 'Sort & Filter' section), or

b) Run the following macro

Sub Show_All_Rows()
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

 

[markmzz]

You can try this too (with Advanced Filter and formula - without macro):

Layout

Name	Company	Notes1	Notes2	Notes3		Notes1	Notes2	Notes3		LookupTxt
Evans, Sara	Marco Polo	Sold	Bought	RND		*RND*				RND
Lost, Marcus	Deadbeat	Bought	RND	Sold			*RND*
Cyrus, Bill	Linksbeat	Sold	Bought	RND				*RND*
Marshall, Becky	Hero	RND	Sold	Bought
Miller, Dennis	Letgo	Sold	Bought	Sold
Link, Karen	Greatso	Bought	Sold	Bought
Like, Link	Marshmellow	Sold	Bought	Sold RND Test
**************	*************	*********	*********	*************	**	*******	*******	*******	**	**********

<tbody>
</tbody>

Criteria Area:

G1:I4


Formula in Criteria Area:

In G2 and copy to the right and down

=IF((COUNTA($G$1:G$1)=ROWS(G$2:G2))*(COUNTA($G$1:G$1)=COLUMNS($G2:G2)),"*"&$K$2&"*","")

Lookup Txt cell:

K2

I hope that this helps.

Markmzz

 

[oceanorigami]

It worked! Thanks Peter_SSs One thing, I actually decided I don't even need the heading column so when I ran this macro it ends up keeping row 1 even without RND being in it. Can you make it include that?

 

[Peter_SSs]

It worked! Thanks Peter_SSs One thing, I actually decided I don't even need the heading column so when I ran this macro it ends up keeping row 1 even without RND being in it. Can you make it include that?

If you are going to use Advanced Filter, as my macro does, then you must have a heading row. If you don't have a heading row, you need to put one in permanently or else the macro would need to put one in temporarily and remove it again at the end. If you need the macro to put in & take out a heading row, post back & I will make that adjustment.


@markmzz
For what it's worth, you can use a manual Advanced Filter with a formula in a single cell, rather than having to copy across and down and use a multi-cell criteria range.
For example, with your data as shown in columns A:E, and lookup text in K2, remove formulas and headings from columns G:I.
Put this formula in G2:

=COUNTIF(C2:E2,"*"&K$2&"*")

Now do your Advanced Filter with Criteria range G1:G2, noting that G1 is empty.

 

[oceanorigami]

It's fine how it is here, thanks a lot! How did you learn that it looks crazy hard! You helped me a lot here thank you!

 

[Peter_SSs]

It's fine how it is here, thanks a lot! ... You helped me a lot here thank you!

OK, that's good. I think it makes sense to have headings anyway.

How did you learn that it looks crazy hard!

Spent too much time on this forum.

 

[markmzz]

@markmzz
For what it's worth, you can use a manual Advanced Filter with a formula in a single cell, rather than having to copy across and down and use a multi-cell criteria range.
For example, with your data as shown in columns A:E, and lookup text in K2, remove formulas and headings from columns G:I.
Put this formula in G2:

=COUNTIF(C2:E2,"*"&K$2&"*")

Now do your Advanced Filter with Criteria range G1:G2, noting that G1 is empty.

Peter, you are right. I forgot the Calculated Criteria. Thanks.

Oceanorigami, you can try this too:

Layout

Name	Company	Notes1	Notes2	Notes3		CalcCriteria		LookupTxt
Evans, Sara	Marco Polo	Sold	Bought	RND		1		RND
Lost, Marcus	Deadbeat	Bought	RND	Sold
Cyrus, Bill	Linksbeat	Sold	Bought	RND
Marshall, Becky	Hero	RND	Sold	Bought
Miller, Dennis	Letgo	Sold	Bought	Sold
Link, Karen	Greatso	Bought	Sold	Bought
Like, Link	Marshmellow	Sold	Bought	Sold RND Test
**************	*************	*********	*********	*************	**	***********	**	**********

<tbody>
</tbody>

LookupTxt cell:

I2

Criteria Area:

G1:G2

Formula in Criteria Area:

In G2

=COUNTIF(C2:E2,"*"&$I$2&"*")

I hope that this helps.

Markmzz