Find out how to get a formula to get a row, but get it multiple times per row.

Academy ↓

Academy ↓

Free ↓

+ 21

Sign up to get access.

About this Tutorial

Find out how to get a formula to get a row, but get it multiple times per row.

Sheet Resources

Video Transcript

Hey, so this is titled, let's call this every fifth row. No every row five times. Let's call it that. Every row, five times. So this come not Tim's times. So this question actually comes from a customer of better sheets. Taylor I answered his question and he said this actually answered his question, and funny enough, I don't even know exactly his use case for the question, so I've made up my own use case.

What the question was he has a column of information of some amount of data, and he wants to. Get the first row five times. He wants another sheet to have the first row, five times, second row, five times. Every one of these items that he has in a row, he wants on another sheet five times, and so this is what he wants essentially.

Five of those, the first name five times the second name, five times the third name, five times. And in my case, I've made up this use case where you want to create like a really nice, like you have, you have say five or 10, you can change the number to whatever you want. Maybe you have five items that you wanna share based on one thing, and you wanna give it a little header and you have this.

Merge sell here. And if you, essentially you have a sheet of, let's call this actually names. And if you have, let me duplicate this and show you what this looks like. If we delete all of this, typically you might want to say something like sheet names a one, and then, okay. In the second one, you want equals sheet.

Or no, sorry. Names a two, right? And if you copy and paste this, but if you copy and paste this notice, let's see, where's Afton hubs? Let's go here. That's fine. Afton is number seven. So what happened is, It will, Google Sheets will automatically help you go to the next row. If I had done it this way, right?

If I had a one here without, let me do this. If I had it here, we'll delete that one.

We'll get rid of that. So if we had it, here we go. Down the list, right? This is. Exactly the right order from here, but because we are there is LV is the last one there. Always last one there, but because we are skipping five rows essentially Google Sheets isn't helping us. Now it's skipping five people.

So we need to figure out how do we change that? How do we only increment one every five rows? And this solution will help you do any number increment one every 10 rows, increment five every seven row. Whatever your two numbers are, once you know the solution, you'll be able to flip those numbers around.

And so let's do it here first. I'll show you how to do this first and then then you'll see how to do it as well here. Same similar. Actually no, these are two completely separate. Solutions. So you'll find out two solutions to this problem. Okay? So again we want to get this these names five times.

What I do in this case is I use row, and what this does is every row increments, once it is literally all it's doing, is giving you the bro number that it's on, which sounds. Stupid, right? Like why do we need to know that it says it right here? It automatically increments one, but we don't wanna increment one.

We wanna increment five. And so we have to use a little bit of algebra here. So we do divide by five. And you might notice this already. We have 0.2, 0.4, 0.6, 0.81, and then down here we have two. Then we have here three. We have, we're close, right? We need to really round up. Okay, if we just add here, round up formula.

Now we took 0.2, we rounded up to one, we took 0.4, we rounded up to 1.6. We rounded up to 1.8. We rounded up to one. So now we have one, five times 1, 2, 3, 4, 5. We have one five times. Next one we are gonna take round up as well and we're gonna keep going. And now we have the row number, but we have the row number just incrementing.

Every five rows. Again, you can change this to whatever you want. Do you want it every 10 rows? And let's do it that way now. Now we have one 10 times, two, 10 times. I use this all the time when I'm doing business models, when I'm trying to figure out some FCI sequences. If I wanna like throw, throw, show some exponential, like random numbers in there, I will do oh, there's so many fun use cases for using ROE to increment.

Once you can do rand as, oh, this is like really funny. You can do rand times. 1000 divided by and then yeah, this, and then you can do that times a row to get even more random. You can do a lot of really cool stuff here. Oh, if you wanna do one, if you wanna have 10 numbers that are between one and 10, 10 random numbers that are between two and 10, there's a lot of really cool stuff you can do with this and some fun arithmetic, right?

You can do a hundred so that you have increment one. All the way down to, so we only get 10, one through 10 for a thousand rows. Really cool stuff. But the, we're not done yet, right? We have a number. Great. And we are incrementing it one every five rows. Cool. Now what do we do? One thing is we are gonna have to combine this with the sheet that it's on.

So we'll do equals. Concatenate and we're gonna equal, actually this is names exclamation point, and then B one. And now we have names one. Oh, actually we need to have the column names a great, now we have where we're gonna go, right? So then we use indirect. And we put that in parentheses and now we get the name.

And so we don't even need this, actually, we can put everything in one column. So we just really want the information. We don't wanna have multiple, and this is what we get. So we have here indirect, then we have in concatenate names exclamation point A, which is the column that you want round.

Row, round up row divided by five, and that gets us the answer. And again, you can 10, divide it by 10, divide by seven, and now it'll be every seven rows. You get the same, oops, every seven rows,

and you get the idea every seven rows now is Marcello, then Petra, then Karen. Here. It's five rows. But here's a really cool function or use case, right? If we have these headers and this, I think will help you there as well, if you had okay, that's fine. And again, now we, oh, we need to change this two, do I buy five?

Oh, that is, Okay. Petra. Petra. Nope, that's not fine. Okay, here is the other answer to this question. So we want here's another way you can do this, and in this case I have these row sorry. Yeah. Five of these rows are merged together, and in this case I'm using Index. Okay. So what Index does is it literally says, let's do index.

Reference, let's just do A, and we're literally numbering the row and numbering the column. If we do A to C and we go one, one, we're saying in this area A to C, a, 1, 2, 3, 4, 5, give me the first row and the first column, which is gonna be right here. Okay? So whatever we put here, hello? He's gonna go here and then if we want the second column, it's Marcella Clu.

If we want the, whoops, if we want the fifth row of the second column, it'll be there. We probably don't have it.

If we want the fifth row of the first, third row of the first column, it's gonna be, hello there. So this gives us a really interesting way, like another different way to find out where we're gonna go to get these names right, to take these names and apply them to these. Essentially hetero rose. And maybe you have some different data that you wanna apply to each person or similar data.

But you need five pieces of data for each person or category or whatever you're using for. And what I did here is I did the exact same thing. I actually will do this, we'll merge this again, and then B paste down the whole way. And so we can do that again. We can say, Equals row and then divided by ten five, sorry.

And we're gonna round it up. Round up. So we see one and then here. Oops. Two, then three, then four. So now, even though those cells are merged, if we had not done this right, we would've had, so we would've had. One, then six, then 11, then 16. We don't want that. We want one, then two, then three. So let's go back.

Let's do that. We have 1, 2, 3, 4. But how do we get the names? We use index, right? We saw it here. And we just replace this with Roe. We go index and we go names exclamation point A to A, a, colon a. What's Rowe? We want that one. And now we have the names and we have Marcello, Petra, Karen, Sarah, Carol, Marcello, Petra, carrier, or Sarah.

I don't think that's Sarah. And that's two answers to getting the same row every row five times. Hopefully this was really helpful for you and interesting. It helped Taylor with his use case, whatever his use case was. Enjoy this solution of getting every row five times. Bye.

What the question was he has a column of information of some amount of data, and he wants to. Get the first row five times. He wants another sheet to have the first row, five times, second row, five times. Every one of these items that he has in a row, he wants on another sheet five times, and so this is what he wants essentially.

Five of those, the first name five times the second name, five times the third name, five times. And in my case, I've made up this use case where you want to create like a really nice, like you have, you have say five or 10, you can change the number to whatever you want. Maybe you have five items that you wanna share based on one thing, and you wanna give it a little header and you have this.

Merge sell here. And if you, essentially you have a sheet of, let's call this actually names. And if you have, let me duplicate this and show you what this looks like. If we delete all of this, typically you might want to say something like sheet names a one, and then, okay. In the second one, you want equals sheet.

Or no, sorry. Names a two, right? And if you copy and paste this, but if you copy and paste this notice, let's see, where's Afton hubs? Let's go here. That's fine. Afton is number seven. So what happened is, It will, Google Sheets will automatically help you go to the next row. If I had done it this way, right?

If I had a one here without, let me do this. If I had it here, we'll delete that one.

We'll get rid of that. So if we had it, here we go. Down the list, right? This is. Exactly the right order from here, but because we are there is LV is the last one there. Always last one there, but because we are skipping five rows essentially Google Sheets isn't helping us. Now it's skipping five people.

So we need to figure out how do we change that? How do we only increment one every five rows? And this solution will help you do any number increment one every 10 rows, increment five every seven row. Whatever your two numbers are, once you know the solution, you'll be able to flip those numbers around.

And so let's do it here first. I'll show you how to do this first and then then you'll see how to do it as well here. Same similar. Actually no, these are two completely separate. Solutions. So you'll find out two solutions to this problem. Okay? So again we want to get this these names five times.

What I do in this case is I use row, and what this does is every row increments, once it is literally all it's doing, is giving you the bro number that it's on, which sounds. Stupid, right? Like why do we need to know that it says it right here? It automatically increments one, but we don't wanna increment one.

We wanna increment five. And so we have to use a little bit of algebra here. So we do divide by five. And you might notice this already. We have 0.2, 0.4, 0.6, 0.81, and then down here we have two. Then we have here three. We have, we're close, right? We need to really round up. Okay, if we just add here, round up formula.

Now we took 0.2, we rounded up to one, we took 0.4, we rounded up to 1.6. We rounded up to 1.8. We rounded up to one. So now we have one, five times 1, 2, 3, 4, 5. We have one five times. Next one we are gonna take round up as well and we're gonna keep going. And now we have the row number, but we have the row number just incrementing.

Every five rows. Again, you can change this to whatever you want. Do you want it every 10 rows? And let's do it that way now. Now we have one 10 times, two, 10 times. I use this all the time when I'm doing business models, when I'm trying to figure out some FCI sequences. If I wanna like throw, throw, show some exponential, like random numbers in there, I will do oh, there's so many fun use cases for using ROE to increment.

Once you can do rand as, oh, this is like really funny. You can do rand times. 1000 divided by and then yeah, this, and then you can do that times a row to get even more random. You can do a lot of really cool stuff here. Oh, if you wanna do one, if you wanna have 10 numbers that are between one and 10, 10 random numbers that are between two and 10, there's a lot of really cool stuff you can do with this and some fun arithmetic, right?

You can do a hundred so that you have increment one. All the way down to, so we only get 10, one through 10 for a thousand rows. Really cool stuff. But the, we're not done yet, right? We have a number. Great. And we are incrementing it one every five rows. Cool. Now what do we do? One thing is we are gonna have to combine this with the sheet that it's on.

So we'll do equals. Concatenate and we're gonna equal, actually this is names exclamation point, and then B one. And now we have names one. Oh, actually we need to have the column names a great, now we have where we're gonna go, right? So then we use indirect. And we put that in parentheses and now we get the name.

And so we don't even need this, actually, we can put everything in one column. So we just really want the information. We don't wanna have multiple, and this is what we get. So we have here indirect, then we have in concatenate names exclamation point A, which is the column that you want round.

Row, round up row divided by five, and that gets us the answer. And again, you can 10, divide it by 10, divide by seven, and now it'll be every seven rows. You get the same, oops, every seven rows,

and you get the idea every seven rows now is Marcello, then Petra, then Karen. Here. It's five rows. But here's a really cool function or use case, right? If we have these headers and this, I think will help you there as well, if you had okay, that's fine. And again, now we, oh, we need to change this two, do I buy five?

Oh, that is, Okay. Petra. Petra. Nope, that's not fine. Okay, here is the other answer to this question. So we want here's another way you can do this, and in this case I have these row sorry. Yeah. Five of these rows are merged together, and in this case I'm using Index. Okay. So what Index does is it literally says, let's do index.

Reference, let's just do A, and we're literally numbering the row and numbering the column. If we do A to C and we go one, one, we're saying in this area A to C, a, 1, 2, 3, 4, 5, give me the first row and the first column, which is gonna be right here. Okay? So whatever we put here, hello? He's gonna go here and then if we want the second column, it's Marcella Clu.

If we want the, whoops, if we want the fifth row of the second column, it'll be there. We probably don't have it.

If we want the fifth row of the first, third row of the first column, it's gonna be, hello there. So this gives us a really interesting way, like another different way to find out where we're gonna go to get these names right, to take these names and apply them to these. Essentially hetero rose. And maybe you have some different data that you wanna apply to each person or similar data.

But you need five pieces of data for each person or category or whatever you're using for. And what I did here is I did the exact same thing. I actually will do this, we'll merge this again, and then B paste down the whole way. And so we can do that again. We can say, Equals row and then divided by ten five, sorry.

And we're gonna round it up. Round up. So we see one and then here. Oops. Two, then three, then four. So now, even though those cells are merged, if we had not done this right, we would've had, so we would've had. One, then six, then 11, then 16. We don't want that. We want one, then two, then three. So let's go back.

Let's do that. We have 1, 2, 3, 4. But how do we get the names? We use index, right? We saw it here. And we just replace this with Roe. We go index and we go names exclamation point A to A, a, colon a. What's Rowe? We want that one. And now we have the names and we have Marcello, Petra, Karen, Sarah, Carol, Marcello, Petra, carrier, or Sarah.

I don't think that's Sarah. And that's two answers to getting the same row every row five times. Hopefully this was really helpful for you and interesting. It helped Taylor with his use case, whatever his use case was. Enjoy this solution of getting every row five times. Bye.

Better Sheets

Something for everyone to Learn Google Sheets. Includes Google Sheets for Beginners and Advanced Google Sheets Tutorials alike.

Google Sheets Help

Google Sheets Courses

Google Sheet Templates and Tools

Google Sheet Add Ons