Lets say I want to filter the wks column by the value of 12, however I want it to show other values if the same student has multiple values and one of these values match 12.
For example see below. ID 555400 has 12,7,7. ID 555800 has 7. So when I filter by 12, it only shows ID 555400 but all values associated with it, so all rows of 12,7,7.
There are certainly ways to do it with macros, but without using macros, you can do it by adding a cell where you store the filter value (let's use F1), and a helper column to identify rows that should be displayed.
So, in F1, put the value you are filtering for:
12
Then put a header in C1:
Show
In C2, this formula, using COUNTIFS to check for matching values with the same ID:
=COUNTIFS($A:$A,A2,$B:$B,$F$1)>0
If your data ends at some determined point (like row 100), then you could improve performance by limiting your COUNTIF there too:
=COUNTIFS($A2:$A100,A2,$B2:$B100,$F$1)>0
Then just filter on column C to TRUE. To change the filter, put a new value in F1, then hit reapply on the filter.
