Google Sheets QUERY: Merge Multiple Columns - Expand Into New Structure (Example)
How do you merge multiple columns and then expand them into a different arrangement using Google Sheets? Using a working example I will demonstrate how to migrate a specific data set containing columns, into a different data set using a different arrangement of columns. The final formula is quite the monster and I’ll dissect this piece by piece to help demonstrate the process: = A R R A Y F O R M U L A ( S P L I T ( A R R A Y F O R M U L A ( S U B S T I T U T E ( T R A N S P O S E ( S P L I T ( J O I N ( " " , Q U E R Y ( D a t a C o n c a t ! A : L , , R O W S ( D a t a C o n c a t ! A : A ) ) ) , " " ) ) , " ~ " , " " ) ) , " ; " , T R U E , F A L S E ) ) Original Data Structure I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year. ...