Excel Find One Hit Wonders With Excel UNIQUE function - Episode 2246

3,234 views · Published 4 October 2018 · 3:18 · Indexed 8 November 2026

Channel: MrExcel.com · 2018 · Education

Watch on YouTube

Microsoft Excel Tutorial: Find Items that Appear Exactly Once in a Data Set. How to use exactly_once argument in Excel.

Welcome back to another episode of the MrExcel netcast! In this episode, we will be diving into the world of Excel's UNIQUE function and how it can help you find one-hit wonders. Yes, you heard that right - we will be using Excel to find those artists who have only had one hit on the Billboard charts. So, let's get started!

First, I want to clarify something I mentioned in our previous episode about the UNIQUE function. This function actually serves two purposes - distinct and unique. For those familiar with databases, you may already understand these concepts. But for those who don't, let me explain. A distinct list is one that contains only unique values, meaning each value appears only once. On the other hand, a unique list contains every value that appears in a list, regardless of how many times it appears. 

Now, let's get into the nitty-gritty of using the UNIQUE function. To get a distinct list, all you have to do is specify the array and the function will do the rest. But, if you want a unique list, you can use the third argument to specify "true" and the function will return only the items that appear once in the list. This is perfect for finding those one-hit wonders we mentioned earlier. 

But that's not all - the UNIQUE function also has a second argument that allows you to compare values across columns. This is useful when you have a list of products and their corresponding ingredients. By using "true" for the second argument, you can get a unique list of all the ingredients used in your products. And to make things even better, you can easily copy and paste the results, unlike with the old array formulas. 

Now, if you want to take things a step further and have a sorted list of unique values, you can use the SORT function in conjunction with UNIQUE. And if you want to have all the unique values in one cell, you can use the TEXTJOIN function. Simply specify the delimiter and ignore empty cells, and you'll have a neat and organized list of unique values. 

If you want to learn more about dynamic arrays and how they can make your life easier, be sure to check out my 60-page eBook, "Excel Dynamic Arrays". And for a limited time, I am offering this book for free until the end of 2018. So, don't miss out on this opportunity and click the "i" on the top right-hand corner to download your free copy. 

Thank you for tuning in to this episode of the MrExcel netcast. We hope you found this tutorial on the UNIQUE function helpful and will use it to find your own one-hit wonders. To download the workbook used in this video, click the link in the YouTube description. And don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#microsoft365
#walkthrough
#excelnew
#excelformula
#excelformulasandfunctions
#evergreen 

This video answers these common search terms:
how to use unique function excel
how to use the unique function in excel
how find unique values in excel
how to find unique values in excel
how do i find unique values in an excel column
how to create list of unique values excel
how to extract all unique values in excel
how to extract unique records in excel
how to extract unique values from a column in excel
how to get unique values from a list in excel
how to get distinct records in excel
how to get distinct values in excel column
how to list distinct rows in excel
how to get a list of distinct values in excel
how to take a list in excel and get distinct values

Table of Contents:
(00:00) Find Items That Occur Exactly Once in a Data Set
(00:15) Understanding the concept of a unique list 
(00:27) Using the Unique function to find one-hit wonders 
(01:23) Exploring the second argument of the Unique function 
(02:05) Combining Unique with Sort and Textjoin for a sorted unique list 
(02:33) Free eBook on Excel Dynamic Arrays 
(02:50) Clicking Like really helps the algorithm

Using the new UNIQUE dynamic array function in Excel to return a list of distinct or unique values. This episode looks at the third and second arguments in the UNIQUE function. Also - using UNIQUE inside of SORT and TEXTJOIN.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1154075/

More from this channel