1

In one column I have a list of comma (and whitespace) separated responses to a question such as "what music genre do you listen to?"

Alternative, EDM, Electronic, Hip Hop, 
Drum & Bass (D&B), Indie, House, R&B, Rap, Rock
Indie, House, R&B, Rap, Rock,
Rap, Rock

I want a function that will return the unique values of the respones. So something like

UNIQUE(SPLIT({A:A})

(Although that doesn't quite work). Desired output is a column of unique like:

Rock
Rap
...
Hip Hop
Christopher Turnbull
  • 915
  • 1
  • 9
  • 23

2 Answers2

2

If your data starts in column A1, try this formula:

=QUERY(SORT(UNIQUE(FLATTEN(ARRAYFORMULA(TRIM(SPLIT(FILTER(A1:A,LEN(A1:A)),",",1,1)))))),"where Col1 <>''",0)

This gets the data in column A, ignoring blank cells, splits the cells on the commas, trims any leading or trailing spaces, "flattens" the resulting columns into one, gets unique values, sorts, them, and then removes any blank rows.

UPDATE: A more efficient version of the above formula is:

=QUERY(UNIQUE(IFERROR(FLATTEN(ARRAYFORMULA(TRIM(SPLIT(A1:A,",",1,1)))))),"where Col1 <>'' order by Col1")

Note that FLATTEN is an unsupported function that may possibly be removed from Google Sheets at some point. There are other ways of performing its function, if necessary.

The caution, provided by Matt King, on the use of FLATTEN.

If this doesn't work for some reason, please share a sample of your sheet.

kirkg13
  • 2,955
  • 1
  • 8
  • 12
  • I didn't know we had FLATTEN – Christopher Turnbull Nov 18 '20 at 17:21
  • The other ways involve QUERY and TRANSPOSE to merge columns into. Search for "flatten transpose split query" on SO, and you'll find some of the amazing formulas that people like player0, and matt, come up with. – kirkg13 Nov 18 '20 at 17:31
  • @ChristopherTurnbull and kirkg13. You can do without the `FILTER`, `SORT` and `LEN` functions. Try `=QUERY(ArrayFormula(IFERROR(TRIM(UNIQUE(flatten(SPLIT(A1:A,",")))))),"where Col1<>'' order by Col1")` – marikamitsos Nov 18 '20 at 17:45
  • 1
    Good point, Marika. I had started with the filter and sort, and then realised he wanted unique also. Once I added the outside query, I should have cleaned up the inside of the formula. I'll add it as an alternative within my answer. – kirkg13 Nov 18 '20 at 18:59
1

use:

=INDEX(UNIQUE(FLATTEN(TRIM(SPLIT(TEXTJOIN(",", 1, A:A), ",")))))
player0
  • 124,011
  • 12
  • 67
  • 124