1

I have a column in a Google Sheet, which in some cases, includes multiple values separated by commas — like this:

Value
A example
B example
C example
D example
A example, E example
A example, F example
G example, D example, C example

I would like to count all occurrences of the unique values in this column, so the count should look like:

Unique value Occurrences
A example 3
B example 1
C example 2
D example 2
E example 1
F example 1
G example 1

Currently, however, when I use =UNIQUE(A2:A), the result gives this:

Unique value Occurrences
A example 1
B example 1
C example 1
D example 1
A example, E example 1
A example, F example 1
G example, D example, C example 1

Is there a way I can count all of the instances of letters, whether they appear in individually in a cell or appear alongside other letters in a cell (comma-seperated)?

(This looks like a useful answer in Python, but I'm trying to do this in Google Sheets)

amatur
  • 317
  • 2
  • 11

2 Answers2

3

try:

enter image description here

Formula in C1:

=INDEX(QUERY(IFERROR(FLATTEN(SPLIT(A1:A,", ")),""),"Select Col1, count(Col1) where Col1 is not null group by Col1 label count(Col1) ''"))

Or, as per the comments, split on the combination instead:

=INDEX(QUERY(IFERROR(FLATTEN(SPLIT(A1:A,", ",0)),""),"Select Col1, count(Col1) where Col1 is not null group by Col1 label count(Col1) ''"))

2nd EDIT: To order descending by count use:

=INDEX(QUERY(IFERROR(FLATTEN(SPLIT(A1:A,", ",0)),""),"Select Col1, count(Col1) where Col1 is not null group by Col1 Order By count(Col1) desc label count(Col1) ''"))

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you so much for this. It almost works — I just slightly miscommunicated the context in my original post. Some of the values in my data actually have spaces within them. So a cell can include "Value 1, Value 2" — which should be parsed as "Value 1" and "Value 2". This code parses it into "Value", "1", "2" — again, based on my miscommunication in the original post. I've updated the post to clarify that the comma seperated values often have spaces in them. Do you know how to adjust your C1 formula to make that work? – amatur Jun 15 '22 at 16:32
  • 1
    @amatur, not a problem. Use the third parameter in the `SPLIT()` function to false to avoid splitting on all character but a comma space instead. – JvdV Jun 15 '22 at 19:19
  • 1
    That did it @JvdV, thank you so much again!! Enjoy a cold one on me!! – amatur Jun 16 '22 at 11:45
  • 1
    @amatur, received! Since it's high summer I will make sure to enjoy =) – JvdV Jun 16 '22 at 12:19
  • One more question on this @JvdV. Is there a way to edit the formula in C1, so column D will be ordered from greatest to least? So A (3), C (2), D (2), B (1), E (1), F (1), G (1) – amatur Jun 17 '22 at 15:37
  • @amatur, It's a simple fix in the query itself to order by count. See edited answer. – JvdV Jun 17 '22 at 17:01
2

Assuming data in A1:A7:

In C1:

=SORT(UNIQUE(FLATTEN(ARRAYFORMULA(SPLIT(A1:A7,", ")))))

In D1:

=ARRAYFORMULA(MMULT(0+ISNUMBER(SEARCH(", "&ColumnCSpilledRange&", ",", "&TRANSPOSE(A1:A7)&", ")),ROW(A1:A7)^0))

Replace ColumnCSpilledRange appropriately.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Thank you so much for this. It almost works — I just slightly miscommunicated the context in my original post. Some of the values in my data actually have spaces within them. So a cell can include "Value 1, Value 2" — which should be parsed as "Value 1" and "Value 2". This code parses it into "Value", "1", "2" — again, based on my miscommunication in the original post. I've updated the post to clarify that the comma separated values often have spaces in them. Do you know how to adjust your C1 formula to make that work? – amatur Jun 15 '22 at 16:33