Unique Values in a Comma Separated List / Unique Values in Multiple Columns

Question

I have some Google Form Data from several surveys that were conducted, where the answer categories shifted across time. I want to find out all the unique choices that were used, followed by the count of all of them.

The count will be easy, but I can't seem to extract a unique list of answers with any of the formulas I've used.

Simply put, I can't separate the values and get unique values for my data, which looks a bit like this:

A1, A2, A3, A7, A8
A2, A5, A3
A3, A7, A15,
A10
A11
A7, A19
etc.

Ideally, it'd look like this:

A1
A2
A3
A5
A7
A8
A10
A11
A15
A19

From here, I could do a countif formula with

Concatenate("*", A1, "*")

I've made a google sheet where I've tried numerous methods: Query, Split with Array Formula, Split with TextJoin and combinations of all of them. But I'm not able to get his all into one Column

Here's the sheet with my attempts: https://docs.google.com/spreadsheets/d/1179QKxGEaDhlejm2D_opdt2TwtdUrtWrbBMCkriBpvE/edit?usp=sharing

Any help would be greatly appreciated!

Answer 1

Use 2 regex.

This first -
---------------------
Context: global
Find: (?:\b(A\d*)\b,?\h*(?=[\S\s]*\b\1\b))+|(A\d*),?\h*
Replace: $2

Then run this one -
---------------------
Context: global
Find: (?:(A\d*)(?=A))
Replace: $1\r\n

After you're done, sort the text alphabetically.
It will then look like this (depends on sort) -

A1
A2
A3
A5
A7
A8
A10
A11
A15
A19

Note - if your regex engine doesn't support lookahead assertions, this method won't work.
If your regex engine doesn't support horizontal whitespace construct \h*, use this
instead [^\S\r\n]*

Answer 2

=ARRAYFORMULA(UNIQUE(TRIM(TRANSPOSE(SPLIT(QUERY(REGEXREPLACE(
 FILTER(Data!A:A, Data!A:A<>""), "(,)( )([A-Z])", "♦$2$3")&"♦",,999^99), "♦")))))

______________________________________________________________

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(QUERY(REGEXREPLACE(
 FILTER(Data!A:A, Data!A:A<>""), "(,)( )([A-Z])", "♦$2$3")&"♦",,999^99), "♦"))), 
 "select Col1,count(Col1) group by Col1 order by count(Col1) desc label count(Col1)''"))