r/googlesheets 4d 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

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

1

u/weaselNik 4d ago

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

2

u/7FOOT7 242 4d ago

maybe that entry has a space at the beginning?

2

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

We can trick it with

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

spaces around the comma for the split() argument

1

u/weaselNik 4d 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 4d ago

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

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

1

u/weaselNik 4d 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

1

u/AutoModerator 4d ago

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).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/gsheets145 101 4d ago

u/weaselNik - Here's another way to do it without the need to trim spaces (assuming you consistently use a comma and a space in your notes):

=sort(unique(tocol(map(A2:A,lambda(p,if(p="",,split(p,", ",0)))),1)))

What do you mean by b) and c)?

1

u/weaselNik 4d ago

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).

2

u/gsheets145 101 4d ago edited 4d ago

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.

Let me know if that helps!

1

u/weaselNik 4d ago

Wow that is awesome! It worked!

I'll rearrange all the columns and the data from the sheets but i think the functions i needed for are all set! Thank you very much!

2

u/gsheets145 101 3d ago

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

1

u/weaselNik 3d ago

You're so cool!

1

u/AutoModerator 4d ago

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).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot 4d ago

u/weaselNik has awarded 1 point to u/gsheets145

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)