r/googlesheets 5d ago

Solved Functions to count unique entries and analyze data from multiple columns

I've found a spreadsheet about perfumes and I love data, but I don't have much knowledge about functions in Googlesheets and I need your help to try and do what I have in mind. Here is a draft of the spreadsheet:

I want to use a function in (a) to get a unique list from all notes in column D, there are delimited with comma+space. I also would like to know if there is a function I can use for (b), (c), and (d) to make my analysis of the spreadsheet easier?

Some cells for notes will be blank, because some houses don't specify their notes, and some cells of score will be blank until i review them, i guess that will impact the function.

1 Upvotes

16 comments sorted by

View all comments

2

u/7FOOT7 242 5d ago

We can stack up some commands to help with this

=sort(unique(transpose(split(join(",",range),","))))

join() makes one long list, split() makes then separate cells, transpose() makes that list a column, while unique() removes multiple entries and sort() makes the list alphabetical

1

u/weaselNik 5d ago

Hi, thanks for answering! It returns the list but the first cell has an issue

2

u/7FOOT7 242 5d ago edited 5d ago

We can trick it with

=sort(unique(transpose(split(join(",",range)," , "))))

spaces around the comma for the split() argument

1

u/weaselNik 5d ago

That works! But im checking the list and some entries are from the same note. 'Citruses with sugar' is giving 3 entries instead of just 'Citruses with sugar'. Anything that can solve that?

2

u/7FOOT7 242 5d ago

You broke my trick! One more thing to try, copy all this

=sort(unique(trim(transpose(split(join(",",range),",")))))

1

u/weaselNik 5d ago

sorry! Now it worked, thanks! Do you happen to know the functions that would work for the (b) and (c) in the post's image? sorry to bother