- [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. …
AuthorOz du Soleil
- Working with spills
- Using the UNIQUE function
- Sorting with dynamic arrays
- Comparing SORTBY and SORT
- Unstacking records with SEQUENCE
- Creating intelligent filters
- Randomizing data with RANDARRAY
- Combining dynamic arrays in drop lists and more
Skill Level Advanced
1. What to Know Before Using the New Functions
Warnings to heed6m 19s
Horizontal SORT3m 33s
9. Putting It All Together
10. Challenges and Solutions
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.