Home

Use Multiple columns as filter

%3CLINGO-SUB%20id%3D%22lingo-sub-900857%22%20slang%3D%22en-US%22%3EUse%20Multiple%20columns%20as%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900857%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20lurking%20in%20the%20forums%2C%20finding%20all%20kinds%20of%20neat%20things%20to%20do%20with%20a%20scheduling%20spreadsheet%20we%20use%2C%20so%20thank%20you%20all%20for%20questions%20and%20responses%20you've%20posted!%3C%2FP%3E%3CP%3EI%20have%20a%20question%20that%20I%20can't%20seem%20to%20find%20asked%20in%20a%20way%20that%20is%20relevant%20to%20me..%20Caveat%3A%20I%20am%20using%20Excel%20365%20for%20Mac%2C%20so%20I%20don't%20have%20power%20query..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20schedule%20people%20in%20a%20list%20with%208%20columns%20for%20how%20many%20possible%20people%20are%20needed%20(Leader%201%2C%202%2C%203%2C%204%20are%20shown%20in%20attached%20image)%3C%2FP%3E%3CP%3EI%20need%20to%20use%20something%20like%20a%20pivot%20table%20with%20filter%20(see%20other%20image)%20to%20pull%20up%20the%20full%20schedule%20for%20%22User%20x%22%2C%20regardless%20of%20which%20position%20they're%20in.%20So%20sometimes%20they're%20scheduled%20in%20column%20J%2C%20sometimes%20L%2C%20etc.%3C%2FP%3E%3CP%3EI%20can%20only%20get%20it%20to%20filter%20based%20on%20one%20column..%26nbsp%3B%3CSTRONG%3EHow%20can%20I%20filter%20for%20user%201%20based%20on%20column%20J%2C%20K%2C%20L%2C%20or%20M%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20may%20be%20way%20better%20ways%20to%20do%20this%2C%20if%20I'm%20attempting%20with%20the%20wrong%20tools%2C%20please%20suggest%20that%20too!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20tips!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-900857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900932%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20Multiple%20columns%20as%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422504%22%20target%3D%22_blank%22%3E%40erikmitchell%3C%2FA%3E%26nbsp%3BYou%20need%20to%20change%20your%20data%20entry%20table%20into%20a%20flat%20table%20where%20there%20is%20only%20one%20column%20for%20%22leader%20type%22%20and%20the%20values%20are%20%22leader%201%22%2C%20%22leader%202%22%2C%20etc.%20Then%20you%20need%20another%20column%20for%20the%20user%20name.%20If%20an%20event%20has%20three%20leaders%2C%20then%20that%20event%20will%20have%20three%20entries%20in%20the%20data%20entry%20table.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETHEN%20you%20can%20build%20a%20pivot%20table%20that%20is%20filtered%20by%20the%20User.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900933%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20Multiple%20columns%20as%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422504%22%20target%3D%22_blank%22%3E%40erikmitchell%3C%2FA%3E%26nbsp%3BGreat%20question.%26nbsp%3B%20Sorry%20to%20hear%20that%20you%20can't%20use%20Power%20Query%2C%20as%20this%20would%20have%20made%20it%20trivial!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20set%20up%20an%20input%20cell%20that%20you%20can%20put%20the%20user%20selection%20in%20O1%20or%20something%2C%20and%20set%20up%20column%20N%20to%20do%20a%20COUNTIF(J2%3AM2%2C%24O%241)%2C%20and%20filter%20on%20the%20'1's%20that%20should%20appear%20whenever%20the%20user%20is%20selected.%26nbsp%3B%20You%20could%20also%20remove%20the%20manual%20filtering%20step%2C%20by%20having%20a%20second%20column%20that%20counts%20the%20cumulative%20number%20of%20times%20that%20the%20'1'%20appears%2C%20and%20then%20use%20an%20INDEX(MATCH)%20to%20pick%20up%20the%20results.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135957i4A48272B6DA07884%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22User%20Pivot.PNG%22%20title%3D%22User%20Pivot.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20work%20for%20your%20question%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-904114%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20Multiple%20columns%20as%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-904114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThanks%20for%20responding%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20What%20do%20you%20mean%20make%20it%20a%20flat%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
erikmitchell
New Contributor

I've been lurking in the forums, finding all kinds of neat things to do with a scheduling spreadsheet we use, so thank you all for questions and responses you've posted!

I have a question that I can't seem to find asked in a way that is relevant to me.. Caveat: I am using Excel 365 for Mac, so I don't have power query..

 

I schedule people in a list with 8 columns for how many possible people are needed (Leader 1, 2, 3, 4 are shown in attached image)

I need to use something like a pivot table with filter (see other image) to pull up the full schedule for "User x", regardless of which position they're in. So sometimes they're scheduled in column J, sometimes L, etc.

I can only get it to filter based on one column.. How can I filter for user 1 based on column J, K, L, or M?

 

There may be way better ways to do this, if I'm attempting with the wrong tools, please suggest that too!

 

Thank you for any tips!

3 Replies

@erikmitchell You need to change your data entry table into a flat table where there is only one column for "leader type" and the values are "leader 1", "leader 2", etc. Then you need another column for the user name. If an event has three leaders, then that event will have three entries in the data entry table. 

 

THEN you can build a pivot table that is filtered by the User.

@erikmitchell Great question.  Sorry to hear that you can't use Power Query, as this would have made it trivial!

 

I would set up an input cell that you can put the user selection in O1 or something, and set up column N to do a COUNTIF(J2:M2,$O$1), and filter on the '1's that should appear whenever the user is selected.  You could also remove the manual filtering step, by having a second column that counts the cumulative number of times that the '1' appears, and then use an INDEX(MATCH) to pick up the results.

User Pivot.PNG

 

Does that work for your question?

@Ingeborg Hawighorst Thanks for responding 

Sorry, What do you mean make it a flat table?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies