Quadrant to List and List to Quadrant

Intermediate

About this Tutorial

We're going to like take some data, twist it and turn it into a different type of like look and feel. And then we're going to do the same thing in reverse.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

FILTER() FIND() IF() IFERROR() JOIN()

Video Transcript

<div>00:00 Hello, better she's members. So this is a tough one because it's two concepts in one video where you are going to wrangle data , we're going to like take some data , twist it and turn it into a different type of like look and feel. 00:15 And then we're going to do the same thing in reverse.

Um, and this is a common thing in Google sheets. 00:21 When you're trying to say, um, get a list of tasks and arrange them in a way that you have some sort of filtering, um, uh, you have some signifier to this either who, who owns what task in this case, we're going to do sort of an I'm. 00:38 Uh, I think it's a Eisenhower matrix important and urgent or not important, not urgent. Um, we have this quadrant and this, this happens a lot. 00:47 We want to move data from a list to a quadrant or a matrix and move it back into a list or vice versa. 00:53 Right?

Um, depends on where you start and how you then determines what the result in the, uh, thing is. So we're going to do list to quadrant first cause that's the easiest and we're just going to use filter function . 01:10 So we joined and filter . Uh, the quadrant two list is actually a little bit harder. Um, but it is doable. 01:18 As you can see here, I have accomplished it, but we're going to, you are going to end up using if formula , if error find join, um, and let's get started. 01:30 So here's, we'll do the easy one first, right? From a list to a quadrant.

The quadrant we're trying to make is this important and not an urgent. 01:39 So there's four options, right? There's four, we have two binaries, right? Important, not important. Or we have urgent and not urgent. 01:48 We are not filling out this grid . What we are doing is we are taking the list. We have a list of all our tasks, and then we give it data of yes or no. 01:58 Okay. So this has no formulas in it. Yes or no. Important. And urgent column B is important. So if it's yes. 02:05 It's. Yes, it is important. Urgent. Yes. It's urgent and Noah's not urgent in column C. Alright.

So here's the solution I'll show you is we're just going to join to, uh, one filter . 02:19 We're going to have two conditions in it. So I'll, we'll rebuild these and hot seconds. So we're going to delete to lead them and let's do it. 02:27 Step-by-step. So first we're going to do a filter work. What are we going to filter ? The range is going to be the, uh, information that we want the tasks in our list. 02:36 We're going to do comma. And then we're going to do, um, for this one, it's the top left corner. So we need our important column list B to B equals. 02:52 Yes.

I need to capital. Yes. And then we need our C column to equal. Yes. As well. And so here we have a filter . 03:04 What are we filtering? We're filtering the, a column and how are we filtering it? We're making sure that everything in that, in the B column is yes. 03:11 And the column is C both. Yes. And let's just double-check that as task one. Right? So back to our list, task one is the only one. 03:20 Now, if I take the task two and I put, oh, yes, yes, these are all urgent yet. 1, 2, 3.

So we go back to our quadrant and it should be, ah, so see our problem. 03:30 Now we have 1, 2, 3, but they're in separate call a separate rows. So how do we fix that at the beginning, we're going to write, join in parenthesis, the delimiter we're going to do. 03:41 You can do a comma here so you can do in quotes, comma. Absolutely. And then you have to put a comma between the dilemma, Nater and the other part of this join. 03:52 But what that's going to do is it's going to look like this.

We really want to like visually see what's in each box, how many are in each box? 04:00 And this is very hard to do. So our join, we're actually going to delete that comma within the quotes. And we're going to do command enter, which is a new line I'm going to hit enter then and look at that. 04:12 Now everything's aligned 1, 2, 3. So let's delete this one and let's do the other three. Okay.

So we're just going to take this entire, um, formula and we're going to, double-click on, we're going to paste it so that nothing moves, nothing changes we have equals joins. 04:31 If we hit enter, it's exactly the same thing. But in this case we want the important column. So the list let's go back to our list. 04:39 B to B. No, that's all. That's the only thing we need to change in this part. So let's double click on our formula and here column B, we just need to delete yes. 04:52 And put in an oh and now 6, 7, 8. So this should be not important. Urgent. So important. No urgent. Yes.

6, 7, 8. 05:08 Is that correct? It's double check. 6, 7, 8. That is perfect. All right. Let's do. Now again, we need to just move. 05:20 We just need to move one of these to know. So it's going to be the C column. Copy paste the formula in here. 05:30 Now, if we, one thing you might accidentally do is if you just copy and paste everything without double clicking, things will move. 05:40 So in this case they don't move because we're just moving down. But if we moved left to right, all of these, a, B and C would move one. 05:47 So it'd be B to B S CDC, D to D.

That would be a problem. So moving down is not a problem, but moving across is, and we're going to get like an arrow. 05:56 I'll show you right here. Did we get an error ? Okay. So let's change this. We know that we don't want this. 06:10 So what are we changing? We are changing the B column to N oh, Nope. That's what we just did. We're Jamie. 06:20 And we're supposed to change as he cop good thing. We're double checking. All right.

So let's change the no back and change C column and oh, and we have N why do we have an, a no matches are found in our filter ? 06:40 So let's give it a match . Let's do, let's say yes, no, There you go. Got test two and three. Now this is even easier than these two is. 06:58 We need to change both of these to know for this last question. Great. So now we have 1, 2, 3, 4, 5, 6, 7, 8, 9. We have all of our tasks in a quadrant. 07:10 We've moved them from Melissa. Now let's say we want to get data this way.

We want to get data in a quadrant and we want to move it into a list that might happen if you're not optimizing for data , right. 07:25 We're separating all our data into different, um, information in this, uh, what in the list. But sometimes we'll have either fill out a graph or fill out a quadrant and just put in the information and for some non Google sheet users or, or, or lesser Google sheet users, that's easier for them to just click and fill in the cell then to create a list or anything like that. 07:50 But no matter what, we can fix this, we can move it.

We just need to do a little bit of a formula and we'll get it. 07:59 So in this case, what I did is I put in some tasks and I, uh, and I separated them with commas. 08:09 It is very important at least to know what is separate, what is the separator ? What is the eliminator? Uh, and that is very, very important as we'll see, we need to use that. 08:20 So here's the answer we're going to have. If, if error find joint, I'm going to build this all up for you right here in a second. 08:28 So don't worry about that bullet. I just want to show you the answer first. All right.

The first thing we need to do is what, all right. 08:39 So I started this with the, find a formula , and this is a funky little formula , and I'll show you what happens. 08:46 Uh, we're going to do equals fine. What are we searching for? We're searching for a three, this task one, where are we searching for it? 08:53 Well, we want to search in the quadrant. Now, what might be, you think might be the easiest is let's just search all through here. 09:01 Let's all, everything, the whole quadrant. And we are going to hit enter, and we will get value.

Y and array value could not be found. 09:11 Okay. So we need to only serve. We cannot search through multiple rows and columns. We can either search multiple rows or multiple columns or one cell. 09:23 So let's say we have one cell here, B2, and we're looking at quadrant here. And we're saying in B2, does this appear? 09:34 And the find function or formula gives us a number one. Why is that one? Let me do, uh, let me do this all. 09:46 You'll be able to see why I think. Okay. I think you can see it in here. When I do task one, I get one task five. 09:56 I get eight.

If I do anything else, I get valued that it's not found well, if we go back to our, um, the answer is the character count. 10:06 So task T so 1, 2, 3, 4, 5, 6, 7, 8, number the eighth character. There is task five. Start that, that lesson. That's what that's telling us is fine. 10:19 It's telling us literally, how many characters into this text? Can you find the phrase task five? Um, and so what we really need to do is we don't need just fine. 10:30 We need to say, um, if that's fine, function is greater than zero.

Then if it's true, we want to say yes, if it's false, we want to say no, cause this is the important, uh, self, uh, right. 10:51 So that's yes. Let me get rid of these values . Now, if we copy paste this, we have to fix this B6 to be to we get yes. 11:06 Yes. But understand that we also have this task three that's missing. So B two and B three. So what you actually have to do is change our B2 here to B two con colon three. 11:28 I see. But that doesn't work because we get a value.

It's, it's an error we actually need to do is we need to find, we need to join. 11:38 We're going to use this common delineator. You're going to, going to join B2 and B3. And now we get a yes. 11:47 So let's look at this join. Let's just look at it separately and see what's going on. So we got one, five and three. 11:59 So it is finding everything in these cells , 1, 5, 3 in this important, putting it all together so we can actually use the, find a formula . 12:10 Once we combine everything, join it all together. So that's what that joins doing.

And so here we go, join, and we're joining everything together. 12:30 Just need to make sure that the parentheses all line up correctly joined. There we go. All right. So what we're doing is we're joining the quadrant, uh, the two parts of the quadrant. 12:44 And we're saying, if you can find the text here, say yes. Okay. Oops. We have to actually hit, enter, not escape. 12:53 So join, get the parentheses. Correct. There we go.

Now we're going to take B2 and B3, and we're going to lock it because we don't want it moving the two and the three, as we copy it down, we don't want it moving, but we do want this first part, a three to, to change to a four. 13:12 And now what's interesting is we get guests value. Yes. Value, error . Yes. Value error . So how do we deal with that? 13:20 Right at the very beginning, we're going to say if error , actually, we don't even need or there. Yeah. Actually we didn't, if error put it inside the, if, if error and what do we want? 13:46 One actually, nothing.

So we want it to be zero. If it's an error that yes doesn't change. But the value becomes no, because it is yet. 13:55 It is zero. Right? So now what would be probably simple is if we take this entire, um, uh, formula and copy it over to urgent and we're done, right, but we're not done because urgent is going left to right. 14:19 Let's go back over here and see urgent is up here, left to, right. So we need this. Yes. So task 1, 5, 2, and six, a 1, 2, 5, and six are all yes. 14:28 To urgent and they're going left to right. Not up and up. We already did the up and down. Okay.

Now we need to do the left and right. 14:35 And so what we need to do is go, this, what we need to do is change the quadrant to, um, B two, two C three, and that becomes, so task one, let's go double-check task. 14:50 One is not urgent. Task one is, is Argent. Aha. Ah, so we need to do B to the C to not be not C3. 15:09 And now we get yes. And we can copy it all the way down. And we see that yes, is our task 1, 2, 5, and six, which is correct. 15:18 And that is the end of this video.

We have changed a quadrant to a list and a list of a quadrant. 15:24 So we've done two things in this video. That one is pretty simple. We just use a couple of a filter and join. 15:33 But if there's quadrant tool a list, we have to go a little bit deeper.

And we ended up using join to join together, everything in one place using fine to find our text that we have to look within there. 15:46 Uh, then we have an if error , uh, to make sure that if there's an error , cause it's not going to find anything, make it a zero. 15:52 And then if anything is over zero, which means it found the character account, that restraint where the string is, then we hit enter. 16:01 We haven't. Yes, we have. We don't hit enter. We yet have a yes, there we go. So we have correctly moved, uh, from a quadrant tool list. 16:11 All right.

Thanks for watching this video. And I hope this helps you, helps you a little bit, make your sheets better.</div>