SOLVED

Sorting rows based on information contained in 5 different columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3040277%22%20slang%3D%22en-US%22%3ESorting%20rows%20based%20on%20information%20contained%20in%205%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3040277%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20spreadsheet%20with%20data%20that%20has%20been%20exported%20from%20our%20practice%20management%20system%20that%20I%20want%20to%20sort.%20In%20the%20practice%20management%20system%2C%20we%20have%20a%20list%20of%20potential%20clients%20and%20the%20date%20that%20they%20were%20added%20to%20our%20waiting%20lists.%20Then%20we%20have%20labels%20assigned%20to%20them%20to%20identify%20which%20waiting%20lists%20they%20are%20on.%20When%20the%20original%20file%20is%20exported%2C%20it%20puts%20all%20of%20the%20labels%20into%20one%20column.%20I%20have%20figured%20out%20how%20to%20get%20that%20data%20separated%20and%20each%20label%20is%20put%20in%20its%20own%20column.%20Now%20I%20want%20to%20be%20able%20to%20sort%20the%20data%20to%20show%20me%20anyone%20who%20is%20on%20each%20waitlist%2C%20and%20then%20filter%20it%20by%20the%20date%20they%20were%20added.%20However%2C%20I%20can't%20figure%20out%20how%20to%20make%20excel%20look%20at%20all%205%20columns%20at%20the%20same%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Jessica_Irish_0-1639668093093.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Jessica_Irish_0-1639668093093.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F334365i7F1EAC34BA3F2705%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Jessica_Irish_0-1639668093093.png%22%20alt%3D%22Jessica_Irish_0-1639668093093.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20for%20example%2C%20I%20would%20like%20any%20row%20that%20has%20%22speech%20therapy%20waitlist%22%20listed%20in%20columns%20I%20-%20M%20to%20be%20filtered%20and%20then%20I%20would%20sort%20that%20data%20by%20the%20date%20added%20to%20the%20waitlist.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Microsoft%20Office%20365%20on%20a%20Dynabook%20Laptop%20with%20Windows%2010.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3040277%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3040311%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20rows%20based%20on%20information%20contained%20in%205%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3040311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1252999%22%20target%3D%22_blank%22%3E%40Jessica_Irish%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20link%20below%20would%20be%20a%20good%20place%20to%20start.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3040305%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20rows%20based%20on%20information%20contained%20in%205%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3040305%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20thank%20you!%20No%2C%20I%20am%20not%20familiar%20with%20Power%20Query.%20Where%20would%20you%20suggest%20I%20go%20to%20get%20a%20beginner's%20quick%20training%20on%20it%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3040302%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20rows%20based%20on%20information%20contained%20in%205%20different%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3040302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1252999%22%20target%3D%22_blank%22%3E%40Jessica_Irish%3C%2FA%3E%26nbsp%3BI%20dare%20to%20suggest%20that%20you%20can%20do%20all%20in%20Power%20Query%20(an%20integrated%20part%20of%20Excel%20since%202016).%20Connect%20to%20the%20original%20expert%20file%20and%20let%20PQ%20do%20the%20rest.%20No%20need%20to%20split%20the%20Labels%20into%20separate%20columns%20in%20Excel.%20Much%20easier%20in%20PQ.%20Then%20%22unpivot%22%20the%20lot%20summarise%20to%20your%20liking.%20Once%20set-up%2C%20you%20can%20repeat%20it%20over%20and%20over%20again%20when%20the%20export%20file%20changes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20exactly%3F%20That%20depends%20on%20the%20exact%20of%20the%20export%20file.%20Are%20you%20familiar%20with%20PQ%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have an excel spreadsheet with data that has been exported from our practice management system that I want to sort. In the practice management system, we have a list of potential clients and the date that they were added to our waiting lists. Then we have labels assigned to them to identify which waiting lists they are on. When the original file is exported, it puts all of the labels into one column. I have figured out how to get that data separated and each label is put in its own column. Now I want to be able to sort the data to show me anyone who is on each waitlist, and then filter it by the date they were added. However, I can't figure out how to make excel look at all 5 columns at the same time. 

Jessica_Irish_0-1639668093093.png

So for example, I would like any row that has "speech therapy waitlist" listed in columns I - M to be filtered and then I would sort that data by the date added to the waitlist. 

 

I am using Microsoft Office 365 on a Dynabook Laptop with Windows 10. 

3 Replies
best response confirmed by Jessica_Irish (New Contributor)
Solution

@Jessica_Irish I dare to suggest that you can do all in Power Query (an integrated part of Excel since 2016). Connect to the original export file and let PQ do the rest. No need to split the Labels into separate columns in Excel. Much easier in PQ. Then "unpivot" the lot summarise to your liking. Once set-up, you can repeat it over and over again when the export file changes.

 

How exactly? That depends on the exact of the export file. Are you familiar with PQ?

@Riny_van_Eekelen thank you! No, I am not familiar with Power Query. Where would you suggest I go to get a beginner's quick training on it?