Turn Data in Many Columns Into 1 Column

Beginner

About this Tutorial

How to turn many columns into one column.

Video Transcript

<div>0:00 Hey, better sheet members, just a really quick video, because I discovered something new today that I had been doing a different way, uh, which is how to turn many columns into one column. 0:12 I've found a new way to do this. And I think it's pretty fun.

Um, but that does take a little bit of explaining. 0:18 So how I explained it before and in another video here on vendor sheets is basically, we're trying to get all of these numbers into the order that they are in, but, you know, generally speaking, you might have some data in different columns and you need to move that into one column. 0:36 So you see here, 1, 2, 3, 4, 5, 6, 7, 8, 9, uh, let's just clean this up 11, 12, 13, 14, 15.

And, and that's the order we want it to go, but we want it to all be in one column. 0:48 So how I used to tell you to do this and which is the simplest sorta to show and explain is you do a three colon use this, um, curly bracket, which is sort of like a Ray formula . 1:02 We do a three to eight, five with a colon in between. We use a semi-colon if you are in a non us, um, placing sometimes you'll have semi-colons in formulas and not Colin's then, uh, I think this might be different for you because this semi-colon is different than normal for US-based Google sheets. 1:24 All right.

And then we're going to do B three, colon B five, and let's just right now, take that and see what happens. 1:32 So see 1, 2, 3, 4, 5, 6, and we can go in and we can add with a semi-colon in between here C three, colon C five, and we get this right. 1:43 We, this is a pretty simple way, um, to get all of these columns, but you have to sort of know, you know, that we want all of these columns, we need to know which columns we need. 1:55 Um, we can't really array this. Like, uh, let's see what happens when we do this. That's not the correct answer. 2:04 Right.

So we really have to use that trick of, of having the semi-colon in between them. Right. Um, we can't just do that simply. 2:13 This is the same thing as this. Okay. Well, I've just recently discovered flood and what flattened does, let's do a three to E 15. 2:23 We're going to get the wrong answer, but for the right reason. So one second. Oh sure. Not 15 five. Very well. 2:31 So flatten what it's doing, let's just look at it right here. So we want 1, 2, 3, 4, 5, 6. It goes 1, 4, 7.

So it's going 1, 4, 7, 10, 13. 2:42 So it's going to cross here and then across here, 2, 5, 8, 11, 14. Okay. So that's the that's how fun does we just need to add one more thing to fix this and right in the middle, what we're going to do, I'll do it right over here. 2:58 So you can see the difference flat. And then in the middle, we're going to do transpose, uh, a three same, uh, range , a three to five, and we're going to end it with two parentheses. 3:12 Let's see what happens now. Okay. We have only one.

We have 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, exactly the order we want. All right. Let's look at what happened. 3:24 There is an edge case. We start the leading numbers in the middle, right. Which is very, very common. Um, when we have some data , uh, we might not fill out everything we might have maybe copied and pasted from another, uh, program. 3:39 And that program didn't have any data validation . So like some people missed out. Um, let's just delete that. And so now you see a bunch of, um, blanks, right? 3:49 Empty rows. Okay. We're going to do one more thing.

Uh, and, and we'll fix this now. What am I about to say and what I'm, I'm going to read it off of another screen. 3:59 So don't think I've memorized this at all. I did not. Um, but what we're going to add is a query around here. 4:05 Um, queries pretty robust. It does a lot of cool things. People find a lot of cool things to do with it. 4:12 And this is one of those cool things.

So we're going to have query and we're going to say, oh, we're going to put the same thing, flan that we had before flat, and then transpose And pause. 4:24 And If you don't remember this, it can just go to the sheet in the description of this video. I have a link to the sheet, go and grab this and you can use it. 4:32 So we have transposed a three to E five. All right. So we have our normal, um, what we got here, but we have those planks, right? 4:42 So we just do comma. We do where , and this is where query goes off the rails.

If you've never used it before column one, which is called C O L one call one call is column is not no. 4:57 So w what does it say in real language is where we get in th this is going to end up in one single column. 5:06 So that's column one and where column one is not, no, we want that information. If it's no meaning, there is nothing there. 5:13 We don't want it. So we're going to hit enter. And there we go. We got 1, 2, 3, 4, 5, 7, 8, 9. Right?

All of those blanks turned into nothing instead of a blink cell. 5:25 Um, this is pretty cool because if you don't even have to memorize this, right, just come to this sheet, I have put the, uh, link in the description from this sheet and grab this all you have to do to change that range . 5:39 Uh, you can come here, grab this formula .

It's query flattened, transpose, and now you can take a bunch of different Columns and move them into one column. 5:50 Again, another Way to do that is, uh, This Array, the, um, query , not query the curly brackets, a three, uh, a five and check out what happens. 6:09 B three B five. All right. So in there, we don't have a blank. Actually. We might have like one second, let's see there, we have a blank. 6:20 So, right. So same thing here.

If you don't want to use the flatten transpose, let's just double-check that around this, uh, we can Do this where one is not, not, And it works the same way. 6:41 So you have your option. If you want to use the curly brackets, or if you want to use query flattened, transport query , curly record still works. 6:51 Go, thanks for watching.

And if you have a really interesting use case for it, if you've already used this, if you see it and like, oh, I need to use this right away. 7:00 And you have a really interesting use case, just, uh, leave a comment in the description below for others so they can see, um, how to use this. 7:08 I think there's a really cool thing. I just discovered very recently, like.</div>