Duplicate Everything For Each Unique Value

Intermediate

About this Tutorial

Great use of SPINTAX. Get the Google Sheet Add-on; SPINTAX for free.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() CONCATENATE() JOIN() SPLIT()

Video Transcript

<div>00:00 All right, so we have a very interesting problem here. We have a post from Jim on I love Google Sheets This is a free Facebook group facebook.com slash group slash. 00:10 I love Google Sheets and The question is a bit in Interesting Because I think this is probably one of the most Best uses of Spintax and I will explain spintax when we get to it But essentially here's the answer and I think this is the answer because the question is a little vague But Jim's asking how 00:32 would I?

Duplicate all of the blue highlighted values for each of the individual numbers in the unique column I'm looking for somebody who knows how to do this. 00:39 Okay, somebody who knows how to do this.

Well the Answers so far in this post have been like I'll do it for you or use Apps Script and it's very vague to say I use Apps Script for this. 00:52 I do that too sometimes and I have done that in the past of like well the answer is Apps Script and like you can probably figure out a way to like formulate some script that will do this and this one looks like yeah at first blush I'm like yeah this is Apps Script you'll probably do a for loop and but 01:08 then I'm thinking wait what is the actual result that he's looking for and and this happens a lot with general posts and a lot of questions that I get at BetterSheets is the question there's a very specific question it's it's it seems like the person is struggling with even wording the question or they're 01:28 using words that I'm like oh I have to sort of do a little bit of translation and in this case I'm going to do a little translation as to what I think the answer is. 01:36 Because look at this.

I think what he means there's this formatted second formatted and status and I think he wants and I may be wrong I'm okay for being wrong but I think you'll be interested in what I think the result is and how we get there but let's get let's get into it here's what I think the answer 01:55 is the or sorry not the answer but the result that he wants is that every single one of them 1 through 20 is repeated for the unique 1 through 4 is repeated and status is repeated so here we have the the number 20 a bunch of times for a bunch of times pending and each of the unique numbers here's ah  02:16 1001 through 120 you can see that here those are the unique numbers and for each of those the unique version so we get a row of each but what this ends up being there are 20 options in row ah column 1, 4 options in ah row 2, 3 options in ah row 3, and 20 options in row ah column the 4th column. 02:42 So how many results is that?

And I think this is where the problem sort of starts to occur is that's thousands of answers . 02:52 If we scroll down to the bottom that's 4,800. How do we get that? I think it's 20 x 4 x 3 x 20. 03:01 Yeah, 4,800. That's how many permutations. And I think what Jim means here, and again I might be wrong, but if, if the result, the correct result should be that there are 4,800 rows of each of these unique numbers with a unique status, with a unique format , two formatted, one formatted, right? 03:22 That is what we're looking for.

And the answer to get there, how we get there is through Spintax. So two things, why Spintax and why, what is Spintax? 03:34 So Spintax is, uhm, spin syntax . You can formulate, uhm, just with text alone, with curly brackets. Brackets and pipes. Certain text in a word, and usually this is for, like, sales, or like cold emails, personalizing cold emails, or whatever. 03:52 Like, it creates a number of unique options based on, you can write, like, here let's do. One second, so here's an example. 04:03 I can write a message, hi, how are you? I can also write, hello, how are you?

Or I can say, hi, how are you doing? 04:18 Or, hello, how are you doing? Right, those are, and I'll put these all on one line, hello, how are you? 04:27 Hi, how are you? These are four permutations. And how we would look at these permutations, so how, actually, are you doing? 04:36 So what we can see here is that the first two, there's two options, hi and hello. The second, there's how are you and how are you doing? 04:44 And those are four permutations of the same sentence, and we can write that in Smith.

We use curly brackets for whatever options we have, so hi, hi, then we have a pipe for the difference between two options, hello, we're going to put that in curly brackets. 05:02 And then we're going to put a comma, because the same thing in all of them is a comma, a space, and then another curly brackets, and we're going to, how are you, and then another pipe, so the pipe is the difference between two options, or more options, we can have more than two, ah, and then how are  05:19 you doing.

See, so, we have now four permutations, but we have created it in only one line, and why is that cool? 05:29 It's because we have Spintax Parser, ah, this is a, ah, free Google Sheet add-on that I made myself because I was like, I need to deal with Spintax. 05:37 Yes, I was using Spintax in Sheets, and I was able to create Spintax, but not sort of parse out all of the options, and I was like, okay, I can create Spintax easy, and actually Chachapiti is really good at saying, hey, take this sentence and Spintax it. 05:51 So like, if I go here I got Chachapiti, and here I can say, turn this into Spintax, and what it's going to do, it's going to result in the curly brackets, right, and here we got this. 06:07 Great.

So we have another example here. Perfect. And look, let's look at that one. Right, the, the, the simple one that I did with two options each is here, and then here this is giving us three options for the first one, three options for the second one, and then also option at the end, which is a little 06:27 weird, but we'll see what this is in a second because we're using Spintax.

Again, completely free Google Sheet add-on , spintax.bettersheets.co, grab it, get it for free install it into your sheets. 06:40 It offers us this we can go up to extensions, spintax, info, but the info is that there are now three custom formulas, and we're gonna do, use one of them, we're gonna do equals spin all. 06:56 Then we're gonna put the a1 in here, and we're gonna get out, hopefully, exactly those four options, those four permutations we have over here, right? 07:06 Hello, how are you doing? Hello, how are you? Hi, how are you doing? Hi, how are you?

Cool thing is about spintax, let me show you something cool here, is we have three options. 07:16 We can spin, which results in one output, which is just a random output of it, great, one.

We have spinall, which offers us outputs every unique result, but then there's also spineverything, which outputs every result and it includes duplicates. 07:36 So just in case there are duplicates, it will put them all out there, which I don't think there's any duplicates in this one, yeah, there's none here. 07:43 But if there are duplicates, it will dedupe them in the spinall, but not spineverything. Okay, that's just a little aside. 07:51 But here's how we go back to our original question. How do we get all of the permutations of this?

Now, uhm. 08:00 What it loo-, what it looks like you might have to do is take this uhm, let's create the spintax for this, and maybe it looks like 1, pipe 2, pipe 3, oh my god, this is gonna take a long time, I'm gonna show you a little trick and then you do the, another one, 1, again, let's do exactly the same thing 08:22 , uhm, 1, 2, 3, 1, 2, 3, another another curly bracket, sold, pipe, active, pipe, pending, end curly brackets, and it's like, okay, we're gonna have to type all these in, and also the result here is not necessarily useful, it's, it's like the numbers all together, okay, but what we can do is add in between 08:47 them a comma.

We can do comma, and then we can do split , and take this text, split it by the comma, true, and now we get each of these things that we have over here, we have them Bye! 09:04 In a new row. Okay, so I hope this is showing you partly the answer. But here's another thing we can do. 09:11 If we have these all, let's look at this. In a column here, what I'm using here is concave. Patinate and join. 09:21 Okay, so let's start off. Equals join. And I'm going to join with that pipe. A3A22. Two.

And so with just join alone we have all the pipes. 09:40 Now all we have to do is add the concatenate around it. So we're going to do concatenate . We need to add the curly brackets. 09:47 So we're going to use concatenate . And we're going to concatenate in quotes. A first curly bracket, comma. Then the join function to get all that stuff. 09:57 And then at the end we're going to do another comma. And in uhh quotes the end bracket. And so now we have literally what we need. 10:06 Without OUT! Having to type all of this out. That's the coolest part of this.

Is we can take any array here, this one through twenty. 10:15 Put it into a uhh piped row. And then add the curly brackets to get spin tax. And so what I'm doing here is Thank you. 10:24 Each of these columns I'm creating the spin tax for it. And then over here in the e column what I'm doing is just concatenating everything with umm commas. 10:35 And saying spin all of this. So we have the commas in the entire thing and it creates 4800 permutations of this. 10:45 I then do split with array formula . So I'm wrapping array formula around this split . So that it does everything.

Cause if in down below nothing else. 10:56 It's just as one formula . So we're doing, let's do this again. We're doing split equals split , taking this text, splitting it by the comma. 11:06 True. We're gonna get one, right? But we want it to do everything we can. Copy paste this all the way down. 11:12 Totally. Really fine. But also we can wrap a ray formula around this. And we just need to change e to t. 11:21 E to colon e. And now we have 4800 permutations of this answer. Now. Again.

This is answering what I think is the question, right? 11:32 Which is create every permutation of this one through twenty, one through four sold active penny and unique numbers. And so we get 4800 of these. 11:40 And we do it all with formulas. We don't have to do app script . We don't have to deal with coding at all in any way. 11:45 All we have to do is make sure we have the spin tax. And this is spin tax dot better she's like oh if you haven't gotten it yet go ahead and get it. 11:53 I'm really really excited about this because I think this is the answer.

But I'm also very scared because this might not be the result. 12:00 That GM once but hopefully GM is watching this and can give a thumbs up down below.

And if I got this wrong let me know what I got wrong and what the actual result is uhm happy to answer questions. 12:12 And if you're not yet part of the group I love Google Sheets go be it if you're not a better Sheets member yet either down below should be linked to better sheets.co become a member because I'm putting this video that you're watching on better on better sheets.co and everyone that has access to this  12:29 video on better sheets.co will get this sheet for free but I'll so you get to spin tax for free go get it. 12:34 Umm and you get every single tutorial I have like 470 tutorials I think they all have links to the sheets in them so you get the sheet you get Apps Script if we write Apps Script but we didn't this time we saved ourselves from writing some code but we did duplicate every thing for each unique value and 12:49 got 4800 results from just this simple stuff and I think this is the perfect example of spin tax if this is the answer. 12:57 Excited.

Thank you for watching.</div>