0:00 Hey, Ben has this pretty funky a question over on Facebook. We're over here on a facebook.com/groups/i love Google sheets. If you are watching this video on YouTube, then come join us.
0:15 We love Google sheets. You probably love Google sheets to then ask this crazy question. So he's trying to convert a CSV file into an SRT file, and he has these, uh, essentially groups of texts in which he needs to enter a new number and iterate through that number.
0:34 Um, every time that there's a blank cell. And so you see here, he's iterating already, and he has to do this for 4,000 lines.
0:45 Let's see if there's another, there's another paycheck. That's a 31 32 33. And this is going to be a bother if you have to do this manually.
0:51 So I actually came up with a pretty, I think, unique solution. Hopefully it helps. It's basically the idea is I broke it down in like, well, we know if it's a blank cell by the is blank formula.
1:04 The only thing we don't know for all of these is what is the last number that was used, because if we knew that, then we could just iterate one.
1:14 We could just add one to that number. So here's how I solve this problem. I first have a bunch of numbers.
1:22 Here are cells with non blinks, and then I, I put some blanks in. Um, and then I added 1, 2, 3, because manually we can add the first one, at least maybe the second one third one.
1:36 But like after that, we don't, we just don't want to do it. We want to make it programmatic or automatic in some ways, this is sort of the semi-automatic way.
1:44 Here's the solution I did is, and I will, I will break this down after I walked through this or I will walk through it after I share, if is blank of this cell B 16 max.
1:57 So in the tr if that is true and it is blank, then max array formula a five to eight 15 and add one, all right, what did I do here?
2:06 I'll explain them. Then I'll break it down. I took an array formula of the 10 cells above it. And I thought that would be like, I guess that would be correct.
2:15 Maybe you might have to increase that or decrease that depending. Then I took all of those numbers and found the max maximum max.
2:23 I found the maximum number and then I added one. And now as we go down, we can see this, uh, formula will change, right?
2:31 We 17 B 16 becomes B 17. It's a copy and paste it. Uh, and then max, uh, eight, five to eight, 15 will become a four to eight 15.
2:44 The first thing I did is I did this and I pasted it and I got a false, but I did get the correct number, but I got all these falses and I had to do one extra thing, which is after the, is, uh, in the, if I had had an extra comma so that there is actually some value for false, which in this case will be nothing.
3:05 And so I did that change and now we have blanks for everything other than a blank row. So if there's an actual blank row, it'll get the maximum number of the neck of the top 10 cells just above it.
3:20 And then add one. So let me break this down a little bit. If you're watching this video, if you're wa if your bed and you're watching this video, there's your solution, a solution, a possible solution.
3:30 If you're watching this video and this makes no sense whatsoever, I'm going to try building this, uh, piece by piece.
3:36 Okay. So first thing we know is this formula is blank. Let's start there and we do B we can, we want in there, this is going to give us a true false statement.
3:50 In this case, it is false. Let's just copy paste this down row by row and see what happens when we actually get a blank.
3:58 We get true. Okay? So this is just a basic, true false it's going to give us a true or a false anywhere we go, we, then we can add an F and we do the same thing is blank.
4:15 Uh, B one, let's do the same one. Now we have two more things we have to deal with. We can put two commas in here and pursue them.
4:23 That's the whole thing. If it's true, meaning if it is blank, it's going to be this first value in this.
4:30 We're going to say, Hey, it's George's and put some text here. If it's false or gonna say, Hey, it's false.
4:39 This doesn't seem much different from what we just did, right from this is blank. It's still giving us one or the other.
4:50 The only difference is that we can edit this. We, we can say what to do when it's true, what to do when it's false.
4:56 And in my case, and this case for the solution, I want nothing to happen. So we'd literally just delete this text.
5:03 Hey falls. So we'll move it over here. We're going to iterate at each word each step of good when you call them.
5:12 So we're going to just delete this a false part And let's see what happens then. So we actually get nothing, which is actually correct.
5:22 We D we want nothing. And now wherever we have a blank, all the way down, it's going to give us, Hey, it's true.
5:29 This is perfect. This is the next step. All right. Now, when it's true, now, here's our problem. We don't want texts.
5:36 We want a number. Well, here we have one. So, well, let's just give us, give ourselves a one to start off with.
5:42 We don't need them to do that automatically. We can find the first blank and we next 4,000 lines. We definitely don't want to do it on a non-automatic automatically, but in this case, okay, we know that in the cells, above it is the number, and let's just do something here.
6:04 Let's do equals array, formula, A one, colon, a six. Why I'm choosing that is I'm in the seventh row. And so this is getting the six, uh, six cells above it.
6:25 And you see where it put it, put it in one here. And so that's not really useful, right? But we got the one.
6:33 Well, how do we get from this array formula? How do we get just one number? And we want the biggest number.
6:43 And in this case, we only have one number, but we only want it in this one cell. Well, we can do a few things we can do, like, um, there's no other, what we want is maps.
6:54 That's the answer here. We want max value of all of these max takes an, either a one thing or an array of things.
7:03 And we haven't arrived because we've used array formula. And now we have one. Now this doesn't really help us, right?
7:11 Because we have other things to deal with, right? We just, one is not the correct answer here. What we need is two.
7:23 So we can just go to the end of this add plus one. And there's two. So now we have taken whatever is up here, found the maximum number and added one.
7:37 Well, how do we put the true false and the, uh, is blank and this together, what we can do, let's move each column.
7:46 Let's go to this column and copy and paste that to G seven. Okay. And now we're going to grab the formula.
7:56 We're going to double click grab, but just the max, not the equal sign. We want everything, but the equal sign.
8:02 We're going to copy that with command C. Now, instead of, Hey, true. We don't want texts there. We want this formula that we just made.
8:12 Max array formula one through six plus one. Let's see what happens. We have a two. Now, if we, the only thing we have to do is copy paste this to the next row.
8:22 And if nothing happens, we are happy. Happy. We are happy. Now here's the big test. Number three, we want this to be number three.
8:32 If we are correct, we have done it correctly. We have a number three. Awesome. I think it might break down if this array doesn't have that four.
8:44 Okay. I thought that might not work, but now we have the correct answer all the way down and we continue to have the correct answer.
8:54 It's just going to take up. Here's not the correct answer. There we go. We've got a seven there, seven, eight, and that's the answer.
9:02 So we have created this formula. We can copy paste the entire column down. It's going to change this <inaudible> is going to change up what the number's going to change up.
9:11 One, this a one, a six is going to all change up one and we will get the correct fit thing.
9:18 I thought this was pretty unique, a solution to this problem. And I also thought it was a pretty interesting, unique problem.
9:25 So hopefully if you'd like this, uh, solution, join us over on the Facebook group. facebook.com/groups/i love Google sheets, but.