0:01 Hello in this video, I had a member actually ask this question of tagging and how to get a tags or keywords, sort of in a list of comma separated, uh, list and a comma separated list.
0:15 Uh, so what we want to do, let's say we have a list of titles. These are all video titles that I have in better sheets, 200 titles.
0:26 And I want to add some tags to that. Um, I want the output to be a comma separated list of tags.
0:34 So something like, uh, design, uh, productivity, productivity, uh, and maybe with some formulas in there, if there are formulas, formulas or scripting, and this is going to take forever, right?
0:51 If I am just typing in these words, uh, what you might do, uh, next and think of that is you would have a list of tags and you create a drop down menu.
1:03 Uh, and if we create a drop down menu, we can use data validation list from a range. We can say tags, a a, and now we have a list of truck to a drop down menu list of tags.
1:17 Well, this saying so good, any as well, because we are only able to add one a tag to each title.
1:26 Well, that's sucks. We want a list of multiple tags. So how do we do that? A one let's do it this way.
1:35 What I'm about to show you is going to also give you the ability, not just to get a list of tags in with comma, separating them, but it will also allow you to keep track of how many videos or how many things are in each tag.
1:51 So in each category, uh, however you determine you need to use this. Maybe you're categorizing things and each thing can be in a different category, multiple categories.
2:02 Um, but you might want to know how much is in each one as well. So we have a list of titles.
2:07 We have a list of tags, all right, you might have categories. You might have other names. So here's one thing we're going to do.
2:16 We're going to create a new tab. We're going to call this, uh, we'll just call this together or combine say, combine.
2:25 And we're going to delete everything except two columns going to increase the size of these columns a little bit now.
2:32 Yeah, a little bit more. And now in each one, we're going to do the same thing we did before with the data validation.
2:40 Okay, we're going to, but on the left side, we're going to have titles. And on the right side, we're going to have tags data validation list from a range.
2:49 This one's going to be titles eight, eight. We definitely want all of this. We want to save. And on the B column, we're going to do the exact same thing.
3:01 We're going to do tags, a colon, a, we got the whole range there. And so in our dropdown menu, we have all of our tags in our AA column.
3:12 We have all of our titles. Now, if you think this is crazy, right, you think, oh my God, I have to scroll through this and figure out which title is, which you don't have to.
3:22 You can actually start typing how now you see it auto complete. So you have added just by using data validation in this dropdown menu, we have added auto.
3:33 So let's look at our first one create. So we just type in create, and there it is, create a two by two matrix.
3:40 And we can say that has design in it. Now, before we fill in anything, we should probably copy and paste this all the way down.
3:50 So we have like a thousand options, right? And we can keep going. We can say, okay, we want to add another tag.
3:56 So we do this. And we say, we want to add scripting. I don't think that has scripting, but this is just an example, search.
4:03 Oh, that has, um, complex formulas. And we can go down this list, right? We can also copy and paste this entire list into here.
4:11 Maybe copy and paste values. If you want to make sure you at least have one per thing. So let's, but I want to show you how to do the comma separated list first, before we go too far.
4:24 And if we go anywhere else, so let's create three of these. And then we have these three tags. Okay. So go back to our title page in our tags.
4:36 Let me go equals join. Now, what do we want to join? We want to Jillian and bike comma, but what's the value?
4:42 What are the erase we need to filter. We need to filter the combine. And what is our condition? Our condition is that the, a column is equal to our title.
5:01 And now if you'd look right here, you can see it, a preview of it. And it is what we need.
5:06 We have our comma separated values or comma separated list of tags, uh, right here. And if we copy and paste this down, I don't think we're gonna get anything else.
5:20 Yeah. Cause we don't. We are going to get an error because this, uh, video doesn't exist in our list. So that's one way to sort of clean this up, sort of say, okay, copy and paste this down.
5:31 And at least we're going to get one. If there's no error, but on this tags, I want to add one more thing.
5:38 I want to show you that we can add up all the tags so we can do count. If our counting is this range combined B column, our criterion is data validation, and now we can auto fill.
5:53 And now we see a immediately updated list Of many videos are in each of these categories or these, oh my God.
6:01 I even have non-unique. I think this was supposed to be something else there. So now we can use our combine can even move it to the left maybe.
6:15 And we can add more thing more times to each video. And on our titles page, we have all of the tags on our tags page.
6:26 We have all the count. So now what's interesting too, is if you don't have too many, right? In this particular case, we have 200 titles.
6:36 We don't really want to know exactly in this page, which videos are with which a tablet maybe we want to.
6:45 So we want to do like tag all titles. And what we can do is this is actually fun. Cause we use the transpose.
6:55 We can do transpose and we want to transpose the age column of tags. Now we have a horizontal list or headers of each of the tags and underneath each one we're going to do filter.
7:11 And our range is going to be the combine, a column. And our condition is going to be that the B column is equal to a one, which is right here.
7:28 This data validation, we don't have any data validation. Okay. We can just lets us add one and go here. Now we see, now we have a title.
7:42 Now this is cool because now we get to see each of the titles in each of the categories and it automatically updates.
7:51 So we use the transpose. Now we use the filter. Now the only issue here, what I'm about to show you, we really need to do be careful here.
7:59 We're going to add an dollar sign in front of each of the columns, but not the a one. And as we copy paste, the a and the B columns, they stay the same, but the S where it's referencing, this is changing.
8:19 So we can copy paste this all the way down. We're going to get some errors because there's no matches in the filter.
8:25 So if we add simple formulas, let's add like to this video, simple formula, we go back to our tags. There it is already there.
8:35 Our tags have been updated. Our title has been updated. Everything's been updated based on this sheet. And all we have to do at the core of this is two columns of data validation, and we get autofill.
8:51 You can do merge cells. Oh, that's, that's a good design One. And now in our titles, if we search for merge, well, we have to copy and paste this all the way down there.
9:03 It is. It automatically shows up here in our tags. It shows up as three counting correctly and here merge cells correctly.
9:13 So then we've gotten three things out of this one. We're doing one thing, right? We're matching each one. Step-by-step just matching.
9:21 That's all we're doing. And we get three things out of it. We get our tags listed. Our titles counted for each tag and our tag titles listed for each tag.
9:32 This is so exciting. Uh, thank you for watching. I hope this helped. I hope this helps you create better sheets and better data.
9:40 Uh, data entry, maybe have a good one. Bye.