Google-sheets – Count unique tags

google sheetsgoogle-sheets-arrayformula

I’m trying to list and count all tags in my table. For some reason, anything after comma, is getting a white space before the word. So if the tag is found on the beginning of a record, it is seen as different tag. example: “Service” and “ Service” are seen as 2 separate values, when they should be one, and not have that white space.

I’ve fiddled with trim, text join, etc. But I always seem to end up with this problem.

—————————————
record1|tag1, tag2, tag3
record2|tag1, tag2, tag3
record3|tag3

Here is the spreadsheet:
https://docs.google.com/spreadsheets/d/1Fg914iNeG6i8TLSim3YFH-GSql9gPgs7eL8F5RZQ_OU/edit?usp=sharing

Here is the formula I was playing with:

The List:

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

The count:

=ARRAYFORMULA(if(countif(transpose(split(CONCATENATE((B2:B9&", ")),", ")),F2:F) = 0, "",countif(transpose(split(CONCATENATE((B2:B9&", ")),", ")),F2:F)))

Best Answer

lostnconfused, try this in G2:

=ARRAYFORMULA(COUNTIF(B$2:B$9,"*"&TRIM(SUBSTITUTE(F2:F5,CHAR(160),""))&"*"))

You've got non-printing ASCII character 160 masquerading as a space (ASCII 32) in there, which seems to be where your problem is coming in.