Nov 10 2020 05:25 AM
I need help with an excel filtering solution that will allow easy search across three columns.
A simplified example is below..
Issue | Owner | Contributor |
Sustainable industrialization | Manufacturing | Zone1 |
Infrastructure investments | Urban Development | Govt Affairs |
Public policy | Govt Affairs | Urban development |
Sustainable Infrastructure | Urban Development | Energy |
Sustainable Agriculture | Food | Manufacturing |
My goal is to be able to identify only the "issues" a particular department is associated with as either an "owner" or "contributor".
E.g. Q: where does Govt Affairs have responsibility?
A: "Owner" against Public policy; "Contributor" against Infrastructure Investments.
So something like this appears...
Issue | Owner | Contributor |
Infrastructure investments | Urban Development | Govt Affairs |
Public policy | Govt Affairs | Urban development |
Normal filtering places a hierarchy on one column first, but I want to show all issues that are relevant across the Owner and Contributor columns.
Any help would be appreciated. Thankyou in advance.
Nov 10 2020 05:42 AM
You can use Advanced Filter for this.
Elsewhere on the sheet, enter Owner and Contributor in two cells next to each other.
Enter Govt Affairs in the cell below Owner, and also two cells below Contributor:
Click in any cell of your data.
On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.
Excel should automatically enter the entire data range in the 'List range box'.
Click in the 'Criteria range' box.
Point to the range as displayed above.
Click OK.
Nov 10 2020 06:23 AM
Thanks for your fast response but that doesn't do anything when I follow your instructions.
Nothing appears. Is Excel for Mac different?
This is the step before hitting OK...then no change at all.
Nov 10 2020 06:39 AM
The Criteria range should not be $A$1:$C$6 but $G$8:$H$10
Nov 11 2020 08:02 AM
@Hans Vogelaar thanks very much that seems to have done the trick.