Google Sheets Tutorial - Counting Unique Values Within Comma Separated Data

Рет қаралды 4,017

Күн бұрын

🚀Join my free tech newsletter: got-sheet.beeh...
How to find unique values in a data set when cells contain more than one value separated by commas. And how to count the number of occurrences of the values.
Dataset scraped from IMDB. Google Sheet linked below 👇
00:00 Intro and Summary
01:12 =UNIQUE() function
01:55 =JOIN() function
02:33 =SPLIT() function
03:00 =TRANSPOSE() function
03:25 =SORT() function
03:40 Nested function for Unique Values
05:30 Finished Result 1
05:55 =COUNTIF() nested function
07:20 Finished Result 2
08:15 Conclusion and subscribe
Google Sheet Demo (make a copy by clicking File - Make a Copy): docs.google.co...
Full article at freeCodeCamp: www.freecodeca...
😀 Please subscribe to my channel for more coding and spreadsheet content: www.youtube.co...
You can find more of my work including articles and podcasts on my website at www.eamonncott...
#googlesheets #excel #spreadsheet ⭐⭐Some of My Computer Desk Gear for Data Analysts (affiliate links support my channel's growth 😀)
►Keychron Q5...my new favorite; it's lovely especially with the quiet keycaps: amzn.to/3NLn57r
►Keychron's Full Product Line...amzn.to/3rtitv7
►DeltaHub Ergonomic Wrist rest. I was skeptical, but it has done wonders for my desk-wrist soreness. amzn.to/3O7cRQi
►Uplift Standing Desk...I love this thing. I also sit at it a lot, which I'm ok with 😀amzn.to/3XPAmAM
►Logitech THX 2.1 Speakers...great sound at an affordable price... amzn.to/3D9InXr
►Phone stand for Desk...surprisingly handy to have phone on a stand. And cheap...amzn.to/3D8ODid

Пікірлер: 31

@TheOxIshere 10 ай бұрын

I have no idea why more people didn't comment on this but I'll be the first to sing your praises. Thanks so much for this. Huge breakthrough on a sheet I'm working that had comma delimited data. I didn't have spaces after my commas but it worked fine removing the spaces in my query.

@EamonnCottrell 10 ай бұрын

Awesome! Glad it was helpful for you; it was incredibly useful for me as well when I was first investigating how to do this earlier in the year. Thanks!

@patsantos1713 3 ай бұрын

This really helped me with my research. Thanks for posting this.

@EamonnCottrell 3 ай бұрын

You’re welcome! Glad to help!

@igorxcx 4 ай бұрын

thank you a lot for this one - this is genius!

@EamonnCottrell 4 ай бұрын

You’re welcome!

@shankarudhaya1142 8 ай бұрын

Thank You So much.... Very grateful for this session...

@EamonnCottrell 8 ай бұрын

You’re welcome!

@cfzornada 4 ай бұрын

Thanks

@EamonnCottrell 4 ай бұрын

You’re welcome!

@BrockRiggs 3 ай бұрын

This is very helpful, but I'm still struggling to apply this. How would you do this if your data range is not the genres you have here but, say, a list of names (first and last) separated by commas? For example, say you have a table listing movies and you have a column listing the actors in that movie where each actor is separated by a comma. If I type the formula exactly as you've done, I get a list containing one name per row (e.g., "John" in one row and "Smith" in the next, rather than "John Smith" in one row). If I delete the space after the comma at the very end of the formula (before the closing parentheses), then the names stay together, but I get a bunch of rows with a leading space (" John Smith") followed by rows with no leading space ("John Smith"). I guess this has to do with the fact that "John Smith" may be the first name in some cells of the range, but may follow a comma in other cells in the range? But I'm not sure how to fix it. Alternatively, how would you get a list of unique names and count the number of occurrences of those names in an array? For example, counting the number of times "Ian McKellen" appears in Columns K through N on the data tab of your example spreadsheet.

@EamonnCottrell 3 ай бұрын

Hey there, could you share your sheet with me? It should work ok even with first and last name as long as they're always separated by a comma and space. I added a tab called "Using Names" in the sample sheet I used to start testing that with some random names. I may be missing something, though. For counting over an array, you could simply take the array and turn it into a column and then use the same technique as in the video. You'd use TOCOL(K1:N) in your example. This would take that array and just plop it into a single column. Happy to check out your sheet if you share it with my full name at gmail

@BrockRiggs 3 ай бұрын

@@EamonnCottrell Thank you so much for getting back to me! I looked at the example you added on Google Sheets and was able to get it working by copying from that. I already deleted my previous attempt from my sheet, so I'm not sure what I did wrong the first time. It's working great now.

@EamonnCottrell 3 ай бұрын

@@BrockRiggs Fantastic! 🙌🙌

@sangitaramaswamy1956 Ай бұрын

This was so helpful! Thank you! Is there a way to have slicers that use the unique comma separate values, but are still linked to the original data set? Ex: Using the slicer to only show the movies that have "drama" as one of their genres.

@EamonnCottrell Ай бұрын

You can create a slicer and then use a custom formula on the Genre column so it displays everything that contains the text, "drama". However, you'd have to manually type that in every time inside the Slicer. A better solution would be what I've done on the Filter Them tab of the Google Sheet. This has a dropdown list that then returns all the movies with the genre you select. Same thing you're trying to do, but more versatile.

@sangitaramaswamy1956 Ай бұрын

@@EamonnCottrell Thank you so much! I've been using excel/google sheets in new ways in my new job, and your channel has been such a lifesaver!

@EamonnCottrell Ай бұрын

@@sangitaramaswamy1956 Awesome! Feel free to shoot me an email if you have any specific needs you'd like to see on a future video (my full name at gmail)

@chrishoooood 8 ай бұрын

Thanks for this tutorial, it's well done! I ran into an issue while doing it, since JOIN creates a cell with more than 50000 characters since I have a super long list of data. Would you know how to achieve this without having to use the JOIN function? Thanks so much!

@EamonnCottrell 8 ай бұрын

Hey good question! Yes, I just added an alternate way to do this by using ARRAYFORMULA(), SPLIT(), TOCOL(), UNIQUE() and SORT(). I put it on it's own "WITHOUT JOIN" tab and you can check it out on the demo sheet here: docs.google.com/spreadsheets/d/1-PfjTvHl2olxAmbrs8LAhP2Dkly9Rp5362YUXIe9RRg/edit#gid=2003711783

@chrishoooood 8 ай бұрын

Thanks so much! @@EamonnCottrell Last thing, I'm sorry I'm a beginner, but could you help adding the CountIF with that method?

@EamonnCottrell 8 ай бұрын

@@chrishoooood Sure thing. I just added a column for that on the WITHOUT JOIN tab for you to check out. It looks like this: =COUNTIF((TOCOL(arrayformula(SPLIT($A$2:$A$1001,", ")))),I2) in cell K2 and then you'd drag it down beside the list of unique values.

@chrishoooood 8 ай бұрын

Amazing!! Thanks you som much!!@@EamonnCottrell

@user-we9xq7gt6w 5 ай бұрын

what if we want the same result with corresponding date.

@EamonnCottrell 5 ай бұрын

Could you elaborate?

@user-we9xq7gt6w 5 ай бұрын

so i have a set of data with date, entity, and a column with values separated by comma. i need each values with the corresponding dates, entity respectively to make a chart. for eg. the table looks like this date entity tag 1st jan 34563 a,b,c,d 1st jan 2738 b,c 2nd jan 26383 a,d,b what i need is this 1st jan 34563 a 1st jan 34563 b 1st jan 34563 c 1st jan 34563 d 1st jan 2738 b 1st jan 2738 c and so on. hope this makes sense.

@EamonnCottrell 5 ай бұрын

@@user-we9xq7gt6w ok this was hard 🤣🤣 .... I made a new tab on the demo sheet for you to check out. This is what the solution looks like: =ARRAYFORMULA(SPLIT(TOCOL(ARRAYFORMULA(A2:A4&"🦊"&B2:B4&"🦊"&SPLIT(C2:C4,","))),"🦊")) Props to @benlcollins for the foxes Check out the sheet here and the formulas you want are in E2 and I2: docs.google.com/spreadsheets/d/1-PfjTvHl2olxAmbrs8LAhP2Dkly9Rp5362YUXIe9RRg/edit#gid=1945437650

@user-we9xq7gt6w 5 ай бұрын

@@EamonnCottrell thank you. Your the best. *hands down*

@user-we9xq7gt6w 5 ай бұрын

@@EamonnCottrell quick question, what does the fox resemble here btw. is it a reference to anything?