Alternative to Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1534421%22%20slang%3D%22en-US%22%3EAlternative%20to%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534421%22%20slang%3D%22en-US%22%3E%3CP%3ELooking%20for%20suggestions%20for%20how%20to%20pull%20all%20data%20from%20one%20worksheet%20to%20another%20based%20on%20a%20value%20in%20one%20or%20two%20columns.%20I%20thought%20I%20might%20be%20able%20to%20do%20this%20with%20Power%20Query%20but%20I%20use%20a%20Mac%20and%20that%20does%20not%20appear%20to%20be%20supported.%20Very%20simplified%20example%20attached.%20(I%20am%20not%20interested%20in%20using%20a%20program%20to%20run%20a%20Windows%20environment%20on%20my%20Mac)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1534421%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1534563%22%20slang%3D%22en-US%22%3ERe%3A%20Alternative%20to%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F734398%22%20target%3D%22_blank%22%3E%40ecgrim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20the%20newest%20version%20of%20Excel%20on%20your%20Mac%2C%20the%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20function%20will%20perform%20exactly%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20you%20add%20SORT%2C%20it%20would%20put%20them%20into%20alphabetical%20order%20(or%20reverse)%20based%20on%20a%20column%20of%20your%20own%20choosing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20rather%20than%20an%20image%20you'd%20upload%20the%20actual%20sample%20spreadsheet%2C%20I%20or%20somebody%20else%20could%20demonstrate.%20But%20my%20guess%20is%20that%20you%20can%20figure%20it%20out.%20In%20fact%2C%20to%20help%2C%20here's%20a%20YouTube%20video%20that%20covers%20these%20new%20functions.%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1534582%22%20slang%3D%22en-US%22%3ERe%3A%20Alternative%20to%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534582%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E!%20I%20appreciate%20your%20guidance.%20If%20you%20have%20time%2C%20I%20would%20love%20a%20demonstration.%20I%20will%20also%20watch%20the%20video%20you%20provided.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1534614%22%20slang%3D%22en-US%22%3ERe%3A%20Alternative%20to%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1534614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F734398%22%20target%3D%22_blank%22%3E%40ecgrim%3C%2FA%3E%26nbsp%3B%2C%20Here's%20the%20Filter%20function%20in%20action.%20I%20converted%20the%20original%20data%20into%20a%20Table%20to%20make%20the%20solution%20more%20robust.%20Hope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Looking for suggestions for how to pull all data from one worksheet to another based on a value in one or two columns. I thought I might be able to do this with Power Query but I use a Mac and that does not appear to be supported. Very simplified example attached. (I am not interested in using a program to run a Windows environment on my Mac)

3 Replies

@ecgrim 

 

If you have the newest version of Excel on your Mac, the FILTER function will perform exactly what you want.

 

And if you add SORT, it would put them into alphabetical order (or reverse) based on a column of your own choosing.

 

If rather than an image you'd upload the actual sample spreadsheet, I or somebody else could demonstrate. But my guess is that you can figure it out. In fact, to help, here's a YouTube video that covers these new functions.  https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

Thanks @mathetes! I appreciate your guidance. If you have time, I would love a demonstration. I will also watch the video you provided.

@ecgrim , Here's the Filter function in action. I converted the original data into a Table to make the solution more robust. Hope this helps.