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.
Question
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.
Regards, Sachin.