Forum Discussion
Solved
How to Split Multiple Columns Into Multiple Rows with Separate Groups of Columns
Hi all. I have a Microsoft Form that gathers all of the interventions that schools use with their students including specific information about each intervention (Type - Academic/Behavior; Subject - Math/Reading; Tier Level - Tier 1/2/3; and Grade Levels K;1st;2nd;3rd;4th;5th;6th;7th;8th;9th;10th;11th;12th). Each intervention (and it's associated characteristics) are in separate columns. How do I transform the data so that the data table looks like the third table below? (The image is also attached).
I want to use PowerQuery and keep everything within Excel (not PowerBI or databases)
Your 1st challenge is exactly the same as Automatic Data entry manipulation where @CWsChim was smart enough not to mark any possible solution so you almost had no chance to find it when you Searched :-((
For your 2nd expected view, the approach is the same. Once you have your first view, Split column [Grade Levels] by Delimiter to Rows then do the same for [Tier Levels]:
Next time(s) please share a sample workbook, recreating data takes unecessary time - Thanks
Your 1st challenge is exactly the same as Automatic Data entry manipulation where @CWsChim was smart enough not to mark any possible solution so you almost had no chance to find it when you Searched :-((
For your 2nd expected view, the approach is the same. Once you have your first view, Split column [Grade Levels] by Delimiter to Rows then do the same for [Tier Levels]:
Next time(s) please share a sample workbook, recreating data takes unecessary time - Thanks
- Type - Academic/Behavior; Subject - Math/Reading; Tier Level - Tier 1/2/3; and Grade Levels K;1st;2nd;3rd;4th;5th;6th;7t
as far as I know need data cleaning with regular expression.
why
not PowerBI or databases)?- Because PowerBI is not accessible to anyone outside of me (the organizations I serve will not have premium licenses), also, I know very very little about how to create a database, but I know a fair amount about Excel. In terms of your feedback, I have no idea what you mean. Could you clarify what you mean by "I know need data cleaning with regular expression"?
- https://answers.microsoft.com/en-us/msoffice/forum/all/merging-multiple-columns-from-one-tab-into-a/18115e49-2d25-49d1-8015-0a61b4f3c5be
https://club.excelhome.net/forum.php?mod=viewthread&tid=1661037&fromguid=hot&extra=&mobile&_dsign=b3762835
In fact,I run online sql to settle this kind of questions.
