Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

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

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!

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

    0

    ______________________________________________________________

    =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)''"))

    0