From the course: Excel: Using Dynamic Array Functions (Microsoft 365)
Unlock the full course today
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Unstack records
From the course: Excel: Using Dynamic Array Functions (Microsoft 365)
Unstack records
- [Instructor] Here is a common need in Excel to unstack a column of records. Here we've got a list of friends and each record has three fields in it. We've got Jerry who is 48 years old, lives in Lisbon, then Myron, 36 years old, lives in Cape Town, and so on. We need to unstack this. And prior to dynamic arrays and a new calculation engine, this could be a really messy affair. There are easy ways of doing this is Power Query, but we don't have to do that. We are going to use the sequence function to help unstack this. We're going to do this in a number of steps. First, let's do the sequence function. Equals sequence. How many rows and columns do we need? We know we need three columns because that's name, age, and city. How many rows? We going to use count A to count the number of rows we need. Count A, open parentheses. We need to count the number in this table. Close parentheses. Comma, how many columns? We need three columns. Close parentheses. Enter. This isn't what we want…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.