Filtering a huge table on one word

[Saschah]

Hey Guys,

I have a pretty big schedule.
It works like this:

I have a lot of subcontractors, they are the column titles of the table.
Then i have the dates in the first column.

In the rest of the table, there are names of places where they need to go.
Because it is pretty big, (its from column A until BR) I weekly try to check if everything is still in place.
So I track every name through the table and see if its still correct.

Now what I wanted to filter the entire table at on lets say PLACE A

So only the collumn and rows with PLACE A are not hidden. PLACE A will be in several columns and lines.


To make it small it'll be like this:


A B C D E
01/01 PLACE A PLACE C PLACE D PLACE F
02/01 PLACE A PLACE B PLACE A PLACE D
03/01 PLACE B PLACE B PLACE F PLACE F
04/01 PLACE C PLACE B PLACE F PLACE A
05/01 PLACE A PLACE C PLACE D PLACE A
......

And i should be able to filter it, just for a moment (i should be able to easely unfilter it) to:


A B D E
01/01 PLACE A PLACE D PLACE F
02/01 PLACE A PLACE A PLACE D
04/01 PLACE C PLACE F PLACE A
05/01 PLACE A PLACE D PLACE A


Can anyone help me, I don't know that what I'm asking is even possible.

THanks!!!

 

[FDibbins]

Not really sure I understand what you are doing here, but maybe add a helper column to check for "place a", then filter on that column?
something like...
=countif(B2:BR2,"place a")..."place a" could also be a cell ref, to give you more flexibility.
Then apply filters and filter on the helper > 0

 

[Peter_SSs]

I don't know that what I'm asking is even possible.

I'm sure it is. To test the concept, try following my steps below. If it is what you want then to apply it to your real sheet, I will suggest some changes to the code and most likely to your sheet layout as well.

A. I'd suggest a brand new workbook with just the data shown above in the range A1:E6.
B. We will make cell H1 the cell where you will enter the "place of interest"
C. Once you enter a value in (or clear) that cell, the appropriate data should automatically show/hide. To get that to happen ..

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering different values in H1, or clearing that cell if you want to see all the data.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Long
  Dim rCrit As Range
  Dim sPlace As String
  
  If Target.Address(0, 0) = "H1" Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    sPlace = Target.Value
    Columns(1).Resize(, Target.Column - 1).Hidden = False
    With Range("A1").CurrentRegion
      On Error Resume Next
      .Parent.ShowAllData
      On Error GoTo 0
      If Len(sPlace) > 0 Then
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).FormulaR1C1 = "=MATCH(" & Target.Address(1, 1, xlR1C1) & ",RC2:RC[-1],0)"
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
        For c = 2 To .Columns.Count
          Columns(c).Hidden = Columns(c).Find(What:=sPlace, LookAt:=xlWhole, MatchCase:=False) Is Nothing
        Next c
        rCrit.Cells(2).ClearContents
      End If
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  End If
End Sub