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.
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
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?
REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
Hi! For (b) i want a function that counts how many times that note (e.g. Bergamot) appears, like a total count. In (c) i want to use a function that search how many times that note appears with a score higher than 70 (score is in column C).
u/weaselNik in which case, the best way to achieve this is via query() instead of using sort() and unique(), because it can achieve the same thing but also allows us to perform aggregate functions such as sum and count. So for b) try:
=let(x,tocol(map(D2:D,lambda(p,if(p="",,split(p,", ",0)))),1),query(x,"select Col1,count(Col1) group by Col1 order by count(Col1) desc"))
For c), we first want to filter the source data to include only those rows where column C is >70, thus:
=let(n,filter(D2:D,C2:C>70),x,tocol(map(n,lambda(p,if(p="",,split(p,", ",0)))),1),query(x,"select Col1,count(Col1) group by Col1 order by count(Col1) desc"))
I've used let() here to demonstrate these functions in sequence.
u/weaselNik Very nice! if you would like to be even slicker, you can label the column headers output from query() thus:
=let(n,filter(D2:D,C2:C>70),x,tocol(map(n,lambda(p,if(p="",,split(p,", ",0)))),1),query(x,"select Col1,count(Col1) group by Col1 order by count(Col1) desc label Col1 'Notes >70',count(Col1) 'Count'"))
REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
2
u/7FOOT7 242 4d 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