1

I'm creating a list of texts with some tags associated to them in Google Sheets, each text has a "tags" column where the tags are separated by commas.

I want to have a second sheet where the individual tags are listed and the "texts" containing that tag will be displayed in a column next to it, each in their own row, leaving a number of blank rows in the Tag column until the next tag. Is this possible at all?

I know I'm probably doing an awful job at explaining so let me illustrate:

This is my main sheet

This is my second sheet so far

This is what I want

This is the function I'm using to list the tags individually (where tagsGrupos is the "Tags" column in the main sheet):

=UNIQUE(TRANSPONER(SPLIT(JOIN(", ",tagsGrupos),", ")))

And this is the function I'm using for the "#" column which counts the number of appearances of each tag (where tagsSeparados is the "Tag" column in my second sheet):

=ARRAYFORMULA(SI(tagsSeparados<>"",CONTAR.SI.CONJUNTO(TRANSPONER(SPLIT(JOIN(", ",tagsGrupos),", ")),tagsSeparados),""))

Edit: Here's a sample sheet, it's the same as the original save that I replaced the hyperlinks with dummy urls

  • It's better to use English for the functions names or at list mention that the functions are in Spanish but copy-pasting to a spreadsheet set to use English will do the "traslation" automatically. – Rubén Jun 10 at 15:17
1

I ended up using the following (where 'listaQuery' it's the range A2:F in the main sheet):

=JOIN(CHAR(10), QUERY(listaQuery,"select B where C like '%"&A:A&"%' order by B"))

this mostly solves the issue except that:

  1. It seems I have to add this code in a new cell every time a new tag comes up in the main sheet
  2. QUERY can't handle hyperlinks, so the hyperlinks copied from my main sheet are converted to plain text

Is there some way I can get the same behaviour using just a single formula (also using functions that handle hyperlinks properly) in the top cell? I've been trying to rewrite this as a FILTER function but I can't manage to get it to work

| improve this answer | |
  • It's fine that you shared you solution even it's not "perfect" but answers should not include follow up questions, they should be posted as new questions. – Rubén Jun 10 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.