My primary readership in Africa are immersed in the celebrations of the season and we’re still in that period where the difference between Monday and Saturday is imperceptible. Needless to say, reading the room might dictate that no one wants to read anything that remotely reminds them of work right now. So I’ll make this quick and painless because I’m sensitive to your needs but also a slave to consistency.
How can you find out how many unique items you have on a list? I know of at least 2 quick ways to do this in Excel and/ or Google Sheets. Here’s how:
1. Remove duplicates
In Excel, to remove duplicate values or entries, select the list, go up to the toolbar, click Data and then click “Remove duplicates” and follow the onscreen prompts. Done!
The sorting of my contrived grocery list below illustrates the process:
2. The Unique function
In Excel 2021/Excel for Office 365 and Google Sheets, the UNIQUE function returns all the unique items in any given range or list with the only compulsory argument in the function being the list/array in question.
The UNIQUE function might be a better alternative because it preserves the original list/array and creates a separate list of unique items from the original list/array.
3. Advanced filter
This feature is extremely powerful and often overlooked in Excel. With the Advanced filter feature, you get the benefits of both Remove duplicates and the UNIQUE function.
For example, if you have a list of groceries and you want to extract the unique groceries without deleting the original list, you can use the Advanced Filter feature. Select the list of groceries you want to filter (exclude the header).
In the toolbar, go to Data, then Advanced, near Sort and Filter and click Advanced.
The Advanced Filter window will pop up where you can determine the range you want to filter, and where you would like the unique values to be placed. In the Advanced Filter window, select "Copy to another location" and check ✅ "Unique records only". In the List range box, enter the range you'd like to filter (mine was $A$2:$A$7) without including the header unless you want the header included in your unique values. In the Copy to box, I entered $B2, but you can enter any cell you'd like the unique values to be placed. $B2 works for me here because it will sit side-by-side with the original list.
Here is the final product:
Conclusion
Extracting the number of branches a company has from a list? Or figuring out how many unique items you spend on monthly; removing duplicates will give you answers instantly.
Let’s leave it here for today. Go back to stuffing your face and postponing your fitness goals.
I wish you the greatest success in all your endeavours. See you in the new year. Cheers!
Comments