Excel filtering and search

%3CLINGO-SUB%20id%3D%22lingo-sub-1871593%22%20slang%3D%22en-US%22%3EExcel%20filtering%20and%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871593%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20with%20an%20excel%20filtering%20solution%20that%20will%20allow%20easy%20search%20across%20three%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simplified%20example%20is%20below..%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22555%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22224%22%20height%3D%2221%22%3E%3CSTRONG%3EIssue%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22164%22%3E%3CSTRONG%3EOwner%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22167%22%3E%3CSTRONG%3EContributor%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2227%22%3ESustainable%20industrialization%3C%2FTD%3E%3CTD%3EManufacturing%3C%2FTD%3E%3CTD%3EZone1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EInfrastructure%20investments%3C%2FTD%3E%3CTD%3EUrban%20Development%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%3EGovt%20Affairs%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EPublic%20policy%3C%2FTD%3E%3CTD%3EGovt%20Affairs%3C%2FTD%3E%3CTD%3EUrban%20development%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3ESustainable%20Infrastructure%3C%2FTD%3E%3CTD%3EUrban%20Development%3C%2FTD%3E%3CTD%3EEnergy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3ESustainable%20Agriculture%3C%2FTD%3E%3CTD%3EFood%3C%2FTD%3E%3CTD%3EManufacturing%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20be%20able%20to%20identify%20only%20the%20%22issues%22%20a%20particular%20department%20is%20associated%20with%20as%20either%20an%20%22owner%22%20or%20%22contributor%22.%3C%2FP%3E%3CP%3EE.g.%20Q%3A%20where%20does%20Govt%20Affairs%20have%20responsibility%3F%26nbsp%3B%3C%2FP%3E%3CP%3EA%3A%20%26nbsp%3B%22Owner%22%20against%20Public%20policy%3B%20%22Contributor%22%20against%20Infrastructure%20Investments.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20something%20like%20this%20appears...%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22555%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22224%22%20height%3D%2221%22%3E%3CSTRONG%3EIssue%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22164%22%3E%3CSTRONG%3EOwner%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22167%22%3E%3CSTRONG%3EContributor%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EInfrastructure%20investments%3C%2FTD%3E%3CTD%3EUrban%20Development%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%3EGovt%20Affairs%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EPublic%20policy%3C%2FTD%3E%3CTD%3EGovt%20Affairs%3C%2FTD%3E%3CTD%3EUrban%20development%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ENormal%20filtering%20places%20a%20hierarchy%20on%20one%20column%20first%2C%20but%20I%20want%20to%20show%20all%20issues%20that%20are%20relevant%20across%20the%20Owner%20and%20Contributor%20columns.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%20Thankyou%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1871593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1871719%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filtering%20and%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374273%22%20target%3D%22_blank%22%3E%40rebeccap2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20Advanced%20Filter%20for%20this.%3C%2FP%3E%0A%3CP%3EElsewhere%20on%20the%20sheet%2C%20enter%20Owner%20and%20Contributor%20in%20two%20cells%20next%20to%20each%20other.%3C%2FP%3E%0A%3CP%3EEnter%20Govt%20Affairs%20in%20the%20cell%20below%20Owner%2C%20and%20also%20two%20cells%20below%20Contributor%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0014.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232582i04AFF72E4EFB6A64%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0014.png%22%20alt%3D%22S0014.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20in%20any%20cell%20of%20your%20data.%3C%2FP%3E%0A%3CP%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20in%20the%20Sort%20%26amp%3B%20Filter%20group%2C%20click%20Advanced.%3C%2FP%3E%0A%3CP%3EExcel%20should%20automatically%20enter%20the%20entire%20data%20range%20in%20the%20'List%20range%20box'.%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20'Criteria%20range'%20box.%3C%2FP%3E%0A%3CP%3EPoint%20to%20the%20range%20as%20displayed%20above.%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0015.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232586i0600816D119D9A6B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0015.png%22%20alt%3D%22S0015.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1871822%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filtering%20and%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1871822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20fast%20response%20but%20that%20doesn't%20do%20anything%20when%20I%20follow%20your%20instructions.%3C%2FP%3E%3CP%3ENothing%20appears.%20Is%20Excel%20for%20Mac%20different%3F%3C%2FP%3E%3CP%3EThis%20is%20the%20step%20before%20hitting%20OK...then%20no%20change%20at%20all.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rebeccap2021_0-1605018137896.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232595i28740C517C84EFD5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rebeccap2021_0-1605018137896.png%22%20alt%3D%22rebeccap2021_0-1605018137896.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I need help with an excel filtering solution that will allow easy search across three columns. 

A simplified example is below..

IssueOwnerContributor
Sustainable industrializationManufacturingZone1
Infrastructure investmentsUrban Development Govt Affairs
Public policyGovt AffairsUrban development
Sustainable InfrastructureUrban DevelopmentEnergy
Sustainable AgricultureFoodManufacturing

 

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...

IssueOwnerContributor
Infrastructure investmentsUrban Development Govt Affairs
Public policyGovt AffairsUrban 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.

4 Replies

@rebeccap2021 

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:

 

S0014.png

 

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.

 

S0015.png

@Hans Vogelaar 

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.

rebeccap2021_0-1605018137896.png

 

 

@rebeccap2021 

The Criteria range should not be $A$1:$C$6 but $G$8:$H$10

@Hans Vogelaar thanks very much that seems to have done the trick.