Excel Data Checking + Row Filter

Question

I have an excel table like so (simplified):

ID       1/18       2/18         3/18       4/18      5/18      6/18
 1        T           T            T          T         F         T       
 2        T           T            T          T         T         T       
 3        T           F            T          T         T         T

T and F are displayed based on an if-statement that compares two sheets -- aka the result of person 1 in all periods but 5/18 are identical across the two sheets.

What I am trying to do now is to try and rectify/check instances where the two are not the same. As my table is huge, I was hoping to so some sort of a row filter where I can get something like this:

ID           2/18        5/18 
 1             T           F         
 3             F           T  

Here I am only interested in looking at person 1 and 3 in periods 2/18 and 5/18 since there are errors there.

Answer 1

This is not the most sophisticated VBA code but works for me:

Sub filter1()

Dim lrow As Long
Dim lcol As Long

lrow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

For i = 1 To lcol
    Cells(1, i).Select
    If Application.WorksheetFunction.CountIf(Columns(i).EntireColumn, "F") = 0 Then
    Columns(i).Hidden = True
    Else
    Columns(i).Hidden = False
    End If
Next i


For j = 1 To lrow
    Cells(j, 1).Select
    If Application.WorksheetFunction.CountIf(Rows(j).EntireRow, "False") = 0 Then
    Rows(j).Hidden = True
    Else
    Rows(j).Hidden = False
    End If
Next j


End Sub

If anyone has a better way to write this code I would love to hear.

Answer 2

If you are comfortable with VBA, you can use the following code. First select the date columns then run the sub:

Sub hideCols()
  Dim col as Range
  For Each col in Selection
     If Application.WorksheetFunction.CountIf(col.EntireColumn, “F”) = 0 then col.EntireColumn.Hidden = True
  Next col
End sub

Answer 3

According to the following sample image, put this in J1 and drag right an additional 5 columns.

=IFERROR(AGGREGATE(15, 7, ($B$1:$G$1)/(INDEX($B$2:$G$9, MATCH($I$2, $A$2:$A$9, 0), 0)<>INDEX($B$2:$G$9, MATCH($I$3, $A$2:$A$9, 0), 0)), COLUMN(A:A)), "")

Put this in J2 and drag right and down.

=IFERROR(INDEX($B$2:$G$9, MATCH($I2, $A$2:$A$9, 0), MATCH(J$1, $B$1:$G$1, 0)), "")