tl;dr How can we force Excel to lock-in a filter for a Column which only has 1 unique value within it?
I am having a very hard time coming up with a nice "label" for this problem, so I believe it is best if I do it with an example. In these examples, I am using an excel sheet where Column 1 represents a list name and Column 2 represents values within each list.
Example that works
I first filter on column 2, searching for "Royalties".
Now, I see that multiple lists from Column 1 match. I am only interested in one of them, so I apply a filter to column 1.
Finally, I want to see all values that are within that list, so I remove the filter from Column 2.
Example that is frustrating
So, I want to do the same thing except when the filter on Column 2 only results in a single list name in Column 1.
Let's repeat Step 1 from above, but by filtering Column 2 on "Rent or Royalties"
Now, Column 1 only has 1 result. What I would like to do is "lock in" a filter on Column 1 even though it is not necessary. The reason is that, then, I want to remove the filter from Column 2 and see all values that fall within that list.
However, I can find no way to do that. Instead of locking in the filter, Excel won't apply a filter to Column 1. Then, when the filter is removed from Column 2, it shows all values in both columns.
The current workaround is to copy the value from Column 1 and paste it into Notepad (you can't keep it in memory because the filter action wipes the copied value). Then, remove the Column 2 filter and then apply an "equal to" filter to Column 1. However, I am wondering whether there is a better way to do this.
Please note that I would prefer a general solution. The spreadsheets change and I would rather not use a macro / VBScript unless it is generic.
Thank you for taking the time to read this long post.