From the course: Excel: Managing and Analyzing Data

Convert an array to a column with TOCOL - Microsoft Excel Tutorial

From the course: Excel: Managing and Analyzing Data

Start my 1-month free trial

Convert an array to a column with TOCOL

“

- [Instructor] Here's a common need. We have a matrix of data and it's easy to see who played in Match8, right, those five people. But now we want to do something with the data itself. One question is, how many individual players are in here? We've got "Al" at least two times that I can see but I would like a list that has "Al" one time, max one time, and then get a count. Also, how many people have played just one time? Here we're going to use a dynamic array function. We'll go to the player's list and =tocal, open parenthesis. What is the array that I want to turn into a column? I'm going to highlight the range and enter, oh, we got a spill error. Why, because the dynamic array is telling us that it does not have enough room for the result that it has. Click here. Look at that, that line is telling us how many rows it needs. Okay, I slide this out of the way and put it right here. Boom, here is the full result, but what it's done is taken the entire array and put it into a single column with the duplicates and everything. So I'm going to go back here and then watch. Wrap this in Unique, okay. Double-click on it. What array, we already have the array. The tocall function did that for us. And I'm going to close parenthesis. Enter, scroll down. Look at that. No duplicates. Now we'll go back and wrap this in Sort to get this into alphabetical order. Enter, oh, now we see there's a problem. Abe and Abram. We've got to fix that. Go to Abe and change that to Abram. Now watch when I hit Enter. Wow, Abe is gone. Now we want to know who's played just one time. =TOCOL, what's the array, the array is here. Enter, go back =unique, open parentheses, go to the end, column. I don't need the by col component. What I do need is I'm going to hit comma. I need the exactly once. So I want True, close parentheses, Enter, and wrap it in the Sort, open parentheses and close parentheses. Enter, Alec, Michelle, Mo, Ryan have only played one time. Now, let's do account, =counta, open parentheses. Now this is a bit tricky because we are dealing with dynamic arrays. Where do I want to count? I want to count here. That's where the formula is, but because this is of the dynamic array, I need to put the hashtag symbol. Now notice it has selected the entire range of that result. Now Enter, 18 players. I'm going to center that. And what I'm going to do is drag this over and there's nothing there. Good, we have four players who've played exactly one time and then we're going to change. Say Sienna to Gregor, Enter. Gregor is now showing up in the overall players' list. Gregor shows up as someone who's played only one time and our counts have updated. And that's the strength of the dynamic arrays functions where you can get multiple results from one formula and the result updates immediately. You don't have to refresh. And remember if you see that spill error, that just means that the formula needs more room for the result that we asked for.

Contents