10

Supposing all I have is the column A below

               +         +
     A         |    B    |    C
+--------------|---------|----------+
               |         |
  X, Y, Z      |   X     |     3
               |         |
  X, Z         |   Y     |     2
               |         |
  X, Y         |   Z     |     2
               +         +

How do I generate columns B and C - where the B column grabs the unique elements from A, and the C column generates a count of those values.

Community
  • 1
  • 1
Louis93
  • 3,843
  • 8
  • 48
  • 94
  • What is the logic behind column B (if first row is correct)? – NoChance Dec 21 '13 at 15:04
  • Ah sorry for not being more clear. The `B` column grabs the unique elements from A – Louis93 Dec 21 '13 at 15:05
  • How is the value Y represent the unique element in X,Z? – NoChance Dec 21 '13 at 15:07
  • @EmmadKareem Y doesn't represent the unique element in row 2 containing "X, Z". Column B looks at all the types of values in Column A, and then generates a list of them. And the only types of values in Col A are values that are either X, Y or Z. Tell me if that makes more sense. – Louis93 Dec 21 '13 at 15:17

3 Answers3

19

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(",",A:A),",")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

QUERY function

TRANSPOSE function

SPLIT function

JOIN function

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • 1
    Super helpful! Here is a minor modification to the above answer that trims the new lines and whitespace from each of the comma-separated elements: =ArrayFormula(QUERY(TRANSPOSE(TRIM(SPLIT(JOIN(",",A:A),","))&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0)) – Krejko Dec 18 '19 at 15:00
  • That's very helpful, thanks! Do you have an idea on how to use the "where" clause that's referencing another column (not A)? I can't seem to figure out how to extend the "date" parameter of the QUERY function. – Adam Nov 05 '20 at 19:34
1

Without hidden cells is possible to do it with an alternative method than the one proposed by Adam (that did not work in my case). I have tested it with google spreadsheets (from data coming from a google form using multiple selection answers):

=UNIQUE(TRANSPOSE(SPLIT(JOIN(", ";A2:A);", ";FALSE)))

Explanation goes as follows:

  • JOIN to mix all the values from the A column (except A1 that could be the header of the column, if not, substitute it by A:A) separated by a coma
  • SPLIT to separate all the mixed values by their comas
  • TRANSPOSE to transformate the column into rows and viceversa
  • UNIQUE to avoid repeated values

Take into account that my "," coma includes and space character i.e., ", " to avoid incorrect unique values because "Z" y not equal to " Z".

Marino Linaje
  • 582
  • 5
  • 8
0

Is it possible to create a hidden sheet?

If yes, 1) use the SPLIT() function to separate the values into columns and 2) use COUNTIF() on the hidden sheet to get the number of values

Chris
  • 868
  • 7
  • 6