1

I have a sheet of names containing two columns: name and tags. Tags are separated by commas plus optional spaces. More than one tags can be in a single cell. Like this:

Name    | Tags
--------+--------------------------------
Alice   | female, fast, archer, starred
Bob     | male, starred
Cecile  | female, support

The sheet contains hundreds of names and a 0..5 tags for each name. As output, I need a list of tags and the number of their occurrences. Something like this:

Tag     | Occurrences
--------+------------
female  | 2
fast    | 1
archer  | 1
starred | 2
male    | 1
support | 1

How can I do this? I've been googling for hours, but couldn't find the solution so far.

1
  • There is no hard limit. But their number probably will never go above 10.
    – Khardan
    Commented Apr 12, 2017 at 9:56

1 Answer 1

2

If your tags occur in Col B (starting at row 2), paste the below formula in column C (row 2) to get all unique tags in the Col B

=unique(transpose(split(CONCATENATE(arrayformula(B2:B&",")),",")))

To get the number of occurrence of certain tag in col B, paste the below in col D row 2

=ARRAYFORMULA(if(countif(transpose(split(CONCATENATE((B2:B&",")),",")),C2:C) = 0, "",countif(transpose(split(CONCATENATE((B2:B&",")),",")),C2:C)))

All the formulas used are standard formulas in Google Sheets.

6
  • I could make the first row working by changing the , to ; in split function and adding a space to the 2nd parameter of the split. The second line gives a parse error. I changed commas to semicolons and added that space, but it did not help.
    – Khardan
    Commented Apr 12, 2017 at 9:58
  • Sorry, I don't really get this whole arrayformula thing. What would the arrayformula do here?
    – Khardan
    Commented Apr 12, 2017 at 10:07
  • For commas and semicolons I found that they are locale dependent. I found this working: =ARRAYFORMULA(if(countif(transpose(split(CONCATENATE(arrayformula(B2:B&",")),", ")),C2:C)=0,"",countif(transpose(split(CONCATENATE(arrayformula(B2:B&",")),", ")),C2:C))) What I added is two more arrayformulas inside concatenate. Thank you for your help!
    – Khardan
    Commented Apr 12, 2017 at 10:29
  • From some reason it does not convert to preformatted text if I start the line with 4 spaces, sorry.
    – Khardan
    Commented Apr 12, 2017 at 10:35
  • @khardan. Firstly, your question indicates that tags are separated by commas. So, I designed the formulas to work on commas! If your tags are separated by semicolons, I would change all the split both first and second function to ; and also make the following change all the B2:B&"," to B2:B&"; ". If you have trailing spaces I would look into using trim function.
    – Jack Brown
    Commented Apr 12, 2017 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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