# Counting the unique values in Sheets rows with comma-separated items

Recently, I had to solve this little (Google) sheets conundrum: if you have rows where one column is a free-text string where multiple terms can be present, in a comma-separated way, how do you get to count all unique values overall?

As an example, suppose you have this sheet:

User Likes
Martina burgers, pizzas, stuffed aubergines
Alice halloumi, beans, stuffed aubergines

namely you have the list of likes each person has, and they appear comma-separated. You want to count the number of unique times each time figures - in this case I share my love of pizza with Bob and my taste for stuffed aubergines with Alice, so these two items are to have a count of 2, everything else has a 1. How do you do this in Sheets? Letâ€™s not talk about the fact that both Bob and Alice appear healthier than me here.

Obviously in raw code (SQL/Python) itâ€™d be an immediate calculation, but in sheets youâ€™d have to create some little machinery (unless Iâ€™m wrong and there is an immediate way, which I didnâ€™t find). In our case (this was a work scenario, and it had to be done in sheet for the use of the team who was using it) the rows for each user came from a form they filled with values and youâ€™d expect that theyâ€™d use a comma to distinguish different items if they were expressing more than one preference (like). We used Google Sheets but I guess the logic is the same or easily adaptable with minimal edits to other spreadsheets tools.

I figured Iâ€™d first explode the list in each row by separating (in a separate tab of the same sheet) each item by working on the commas. Youâ€™d use a SPLIT function for this:

=SPLIT('Preferences'!B2:B1000, ",")


(â€śPreferencesâ€ť is the main tab, the one where the form responses are registered, and B is the column of interest). This will literally create a column for each of the items in the list of a row. After this, youâ€™d want to append each of these new columns to create one single column with all values concatenated. Basically here Iâ€™m trying to translate what Iâ€™d do in code into the Sheets machinery. The problem is that obviously not every user would have specified the same number or items, so in the explosion weâ€™ve done just before not each column has the same number of elements (some are empty). You need to use a FILTER to take care of this, and then apply the said concatenation:

={filter('exploded choices'!A:A, len('exploded choices'!A:A)); filter('exploded choices'!B:B, len('exploded choices'!B:B)); filter('exploded choices'!C:C, len('exploded choices'!C:C)); filter('exploded choices'!D:D, len('exploded choices'!D:D)); filter('exploded choices'!E:E, len('exploded choices'!E:E)); filter('exploded choices'!F:F, len('exploded choices'!F:F)); filter('exploded choices'!G:G, len('exploded choices'!G:G))}


(this is long but itâ€™s because in my case the maximum explosion of a row was yielding 7 values, hence I got 7 columns at the end (A-G)).

VoilĂ , youâ€™re done - you can just apply a count of the unique values onto your new concatenated column. I love SQL all in all so I really donâ€™t mind working with queries in Sheets, sometimes itâ€™s a bit annoying that seemingly easy things may require heavy workarounds but this one was a fun detour and Iâ€™ve learned something.

Tags:

Categories:

Created: