Dynamic Array: Unstack Data with SEQUENCE … just 2 steps

/in /by

Here we are again!
I was playing around more with the Dynamic Array functions, especially after Leila Gharani posted her video that made unstacking data pretty easy with a substitution trick and finding a pattern in the dataset. Here is her video:

Well, I found a 2-step process that REALLY simplifies unstacking a column of data. SEQUENCE, COUNTA and INDEX get us there, taking advantage of spilled arrays and Excel’s new calculation engine. Alas, not everyone has the Dynamic Arrays yet. If you don’t have them, and you need to unstack some data, use Leila’s method. 👍

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2