UNIQUE function in Excel (with 6 examples) | Remove duplicates with a function

UNIQUE function in Excel (with 6 examples) | Remove duplicates with a function | Excel Off The Grid

Рет қаралды 73,055

Күн бұрын

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file:★
exceloffthegrid.com/unique-fu...
★ About this video ★
I can’t even begin to count the number of times I have created a unique list in Excel, all of which used various manual methods. When Microsoft announced dynamic arrays in September 2018, they also announced a host of new functions, one of which is UNIQUE.
UNIQUE is a new function which returns the distinct or unique values from a range or array. This is triggered by the normal calculation process, so is completely automated.
Through 6 examples, you will learn how to master this new function.
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #ExcelOffTheGrid

Пікірлер: 108

@daleanderson5258 3 жыл бұрын

Very good. Thank you. I'm looking forward to the rest of the videos in this tutorial. I'm now going to practice with the example files. Its great that you included them. The videos are great for the basics and the example files allow us to learn by doing.

@ExcelOffTheGrid 3 жыл бұрын

Thanks Dale. Working through examples is the best way to learn. 👍👍👍

@nancydizon734 3 жыл бұрын

Oh thank you so much! I was trying to search how to do such thing in excel and I found your channel. It's amazing to use "UNIQUE" function in excel. It makes work load easier. It is really unique as the name implies. You are a superb teacher! Thank you! Looking forward to your other teaching videos. God Bless!

@ExcelOffTheGrid 3 жыл бұрын

Yes, UNIQUE is pretty cool. Glad I could help. More videos coming soon :-)

@taymaateyah1757 5 ай бұрын

Thank you so much sir, great and simple explaination 🌸🌸

@Chef-1707 3 жыл бұрын

What a great find on utube...downloaded really really helpful examples . Well presented videos and easy to follow steps into formula I had not used before....Appreciate you sharing.

@ExcelOffTheGrid 3 жыл бұрын

Thank you, I appreciate the feedback and support 😀

@playincognito 2 жыл бұрын

Great tutorial. Opens up so many new possibilities in Excel !! Had no idea you could use the # so I worked around it by copying the results from my UNIQUE formula to another area of the worksheet.... Thanks so much !! :-)

@ExcelOffTheGrid 2 жыл бұрын

I think it would be worth you checking out the other videos in the Dynamic Array series. There are lots of new techniques in there which will help you.

@henrytran4782 3 жыл бұрын

Precise and articulate. Thank you very much for sharing your talents.

@ExcelOffTheGrid 3 жыл бұрын

Thank you 👍

@rosswilliams1058 Жыл бұрын

Clearest explanations I’ve found. Great work

@ExcelOffTheGrid Жыл бұрын

Thanks, that’s very kind of you to say. 😀

@nicoleownby2329 11 ай бұрын

Great tutorial! I'm stumped by one thing with an sheet I'm working on and would love help! What if you wanted to a count of something within that unique list? Using the example at 8:20- what if I wanted a count, in row table format, showing how many unique values last names start with (A-E,O-R) and (F-K) and (L-N) and (S-Z)? In this case there would be 3 Ws, etc. I have a list of names and a different person on my team is responsible for last names falling within their alphabet breakdown (shown in the parenthesis earlier).

@mdmueenpasha1637 3 жыл бұрын

This is epic.. the examples were mind blowing ❤️

@ExcelOffTheGrid 3 жыл бұрын

Thanks - 👍

@roberth.9558 Жыл бұрын

Well presented. Thank you for the practice worksheets.

@ExcelOffTheGrid Жыл бұрын

You are welcome!

@spiritualbeingshavinghuman6356 2 жыл бұрын

this is by far, one of the most helpful videos i've seen. thank u so much.

@ExcelOffTheGrid 2 жыл бұрын

Thank You 😀

@juansebastianbuitrago6654 Жыл бұрын

Perfect. No more words. Thank you for you explanation.

@ExcelOffTheGrid Жыл бұрын

You’re welcome.

@SamiElzaim 2 жыл бұрын

Very instructive videos! Thanks and keep on sharing please.

@ExcelOffTheGrid 2 жыл бұрын

There will be more coming soon 👍

@Sri-Nivas 3 жыл бұрын

You are the true master in excel DA.. Want more excel topics from you..

@ExcelOffTheGrid 3 жыл бұрын

More videos are coming - I'm just working on another project at the moment. Once that's done, there will be more.

@Sri-Nivas 3 жыл бұрын

@@ExcelOffTheGrid Happy to hear this.. Need to cover on all the topics.. And I will check out each and your videos.. Eagerly waiting for the next video...

@AmitKasarLive 2 жыл бұрын

Very informative video, Learn lots of new stuff from this.

@ExcelOffTheGrid 2 жыл бұрын

Thank you. Glad to hear it!

@DROIDFARM Жыл бұрын

Liked and subscribed! Thank you!

@ExcelOffTheGrid Жыл бұрын

Awesome, thank you!

@grandpasaysrelax175 Жыл бұрын

Thanks a lot Man!

@ExcelOffTheGrid Жыл бұрын

You’re welcome ✅

@josuezuniga5347 Жыл бұрын

You're awesome! Thanks for the video

@ExcelOffTheGrid 11 ай бұрын

You’re welcome.

@raghadshahir9966 Жыл бұрын

so helpful , Thank you !

@ExcelOffTheGrid Жыл бұрын

Thank You 😀

@MrSupernova111 9 ай бұрын

Bro, that was awesome! Thanks!!

@ExcelOffTheGrid 9 ай бұрын

Glad you liked it! 😀

@sumardjo 2 жыл бұрын

thanks this very complete to learn because sample file and user guide already nice and excellent bravo thank you very much

@ExcelOffTheGrid 2 жыл бұрын

Thank you so much, that is very kind of you to say 😀

@Foreman1329 3 жыл бұрын

Great video, thank you

@ExcelOffTheGrid 3 жыл бұрын

You are welcome!

@GeoffreyStewart 2 ай бұрын

Mark, how do you filter for names that repeat in a list but are distinguished by aged dates? Example being a database of past training records. How do you extract only the latest name-date combination (or last training record) using Unique(). There are quite a few suggestions to use a MAXIFS() helper column, [=B2=MAXIFS(B:B, A:A, A2)], (A= Name, B= Date), recording True or False if latest, but can you do an Array-filter combination inside Unique() that gets the table in one formula? I'd really like to be able to pull out whole rows that provides all fields such Name, Co., email, Phone, training date. Thanks in advance.

@abdulrahmanmuslih141 Жыл бұрын

Thanks a lot

@ExcelOffTheGrid Жыл бұрын

You’re welcome.

@MGHxxvi 3 жыл бұрын

Thank you for sharing this knowledge. Continue being awesome.

@ExcelOffTheGrid 3 жыл бұрын

I’ll try 😀

@Melki 3 жыл бұрын

Thank you :)

@ExcelOffTheGrid 3 жыл бұрын

No problem 😀

@rameshhariharan2623 2 жыл бұрын

Im from india great teacher. Great. Thank u so much .

@ExcelOffTheGrid 2 жыл бұрын

Your welcome, Thank you :-)

@anthonycassar1341 3 жыл бұрын

Thank you for this tutorial. I need to pick out unique values from two different columns in two different tabs into one result column. Can Unique function possibly do this?

@ExcelOffTheGrid 3 жыл бұрын

It can, but it's not easy. Check out this video from Mike Girvin: kzfaq.info/get/bejne/ateKa9iVncfPpac.html

@felipeposse2009 3 жыл бұрын

Thank you very much! Hey, if you want to create unique values of names but with de criteria Pass/Fail="Fail", what formula should i use?

@ExcelOffTheGrid 3 жыл бұрын

You need the FILTER function nested inside UNIQUE. I’ve got a video on FILTER too, check it out.

@akaninyenemaurice2702 Жыл бұрын

I just came across tutorial by "excel off the grid" for the first time today. I must confess that i've not really enjoyed online tutorial videos like I enjoyed this. I tried out all the 6 examples using the excel file provided in the description. I am so grateful for what you do that I have decided to subscribe and follow you on twitter. well done.

@ExcelOffTheGrid Жыл бұрын

Glad it was helpful! Welcome aboard.

@Matheus_Oliveira_Xs1 3 жыл бұрын

Awesome video, really helpful! But how did you put that line beneath the words? Hahaha, I'd like to know.

@ExcelOffTheGrid 3 жыл бұрын

If you mean the word underline below the headings, it’s the ‘Single Accounting Underline’ which is applied under the Format Cells dialog, in the Font tab, Underline drop-down. 😀

@Matheus_Oliveira_Xs1 3 жыл бұрын

@@ExcelOffTheGrid Oh, it's exactly that, dude. Hahaha, thank you a lot. I really forgot that completely, I thought it was an Excel border.

@christoslefkimiotis9889 3 жыл бұрын

Very good, I haven't seen the # trick before

@ExcelOffTheGrid 3 жыл бұрын

The # is part of the new Excel calculation engine. Which is currently only for Excel 365 subscribers. Check out this video for more information: kzfaq.info/get/bejne/iqmnmMZpktrXdJ8.html

@fernandogamez1256 3 жыл бұрын

Great video! Have you tried using the UNIQUE formula when your list has blanks? Have you figured out how to exclude those?

@ExcelOffTheGrid 3 жыл бұрын

You can combine the UNIQUE function with the FILTER function. Something like the following would work: =UNIQUE(FILTER(A2:A11,A2:A11""))

@fernandogamez1256 3 жыл бұрын

@@ExcelOffTheGrid thanks that worked! The unique formula is great. only flaw is see with it is that you cant sort by other adjacent columns and you cant use it on a table

@incredible421 Жыл бұрын

@@ExcelOffTheGrid very helpful

@chance.j3290 2 жыл бұрын

thanks for this, just a question. how will you remove duplicate values or text string in a single cell? example, in one single cell i have: cash, dash, bus, dash, crash, cash i need to remove the 2nd cash and dash (duplicate values) is there a way to do it? thanks in advance. 🙂

@ExcelOffTheGrid 2 жыл бұрын

I think you are probably looking for the SUBSTITUTE function. It can find the 2nd instance of an item and replace it with blank.

@roldansumpay5155 2 жыл бұрын

Great video! but what if there is a blank in between cells?

@ExcelOffTheGrid 2 жыл бұрын

Then you get a zero included in the spill array. You can use the FILTER function to remove the zero.

@roldansumpay5155 2 жыл бұрын

@@ExcelOffTheGrid thank you so much!

@Mentalcheez 2 жыл бұрын

How can we use unique for ranges in multiple sheets? Let's say we had name lists in 3 different sheets. How would we adjust the function for it to work?

@ExcelOffTheGrid 2 жыл бұрын

At present there isn't an easy way to do this. You first need to create an complete array for UNIQUE to work on. But creating that array is the problem.

@seanmahoney1077 Жыл бұрын

Just installed Office Professional Plus 2021 and this was the first tutorial. How many hours were spent with INDEX, MATCH, INDIRECT in Office 2019 to get unique lists for validation?

@ExcelOffTheGrid Жыл бұрын

Too many. And you never need to do that again 😁

@user-fy4yd9tz4w 4 ай бұрын

One of, if not the best example of teaching I have ever seen. Can’t thank you enough. I was blown away by the capabilities and versatility of the Unique function.

@piotrkobalczyk2139 Жыл бұрын

How to select unique names from many columns (per range), e.g. we want unique names in the rangeA1;K30?

@ExcelOffTheGrid Жыл бұрын

I think you would need the new TOCOL function which is available in Excel 365.

@oliviershami1874 3 жыл бұрын

Does unique fx work in excel 2013 ? If not what else can use directly, apart from pivot table?

@ExcelOffTheGrid 3 жыл бұрын

Currently it works in Excel 365 and Online only. For 2013 other options are: - Advanced formula: exceloffthegrid.com/list-unique-or-duplicate-values-with-excel-formulas/ - VBA Macro - PowerQuery - Remove duplicates zfrom the ribbon - Advanced filter from the ribbon

@brap5829 Ай бұрын

This says only the first criteria is avaiable in download version link? How where's the paid version so I can add true true because it just keeps repeating the first unique over and over even though there are 6 uniques.

@zhangren1988 Жыл бұрын

4:38 「TRUE」可以換成『1』嗎？

@nikhilsoni1177 2 жыл бұрын

Is thus formula avilable in 2019?? Coz i have the updated version but i can't see this formula can you help on this ???

@ExcelOffTheGrid 2 жыл бұрын

Sorry, it's currently only available on Excel 365.

@mikelennon1078 3 жыл бұрын

Does anyone knows how to search entire workbook and return all records which match a certain value from a drop down list with or without a Macro? e.g. if we have a list of many large organization in a worksheet and have a list of all of their employee, first name, last name, phone, email, title,..... ( many per organization) in another worksheet. How one can see the records of all employees in an organization by selecting the organization's name from a list or drop down box, etc. I would appreciate if your insights.

@ExcelOffTheGrid 3 жыл бұрын

Sound to me that you need the FILTER function: exceloffthegrid.com/filter-function-in-excel/

@mikelennon1078 3 жыл бұрын

@@ExcelOffTheGrid I am using Excel 2019, the video says the Filter function is only available for 365?

@ExcelOffTheGrid 3 жыл бұрын

In which case FILTER isn't going to work for you. Check out this video as an approach that should work: kzfaq.info/get/bejne/nKpyZK6l3s7UY40.html

@mikelennon1078 3 жыл бұрын

@@ExcelOffTheGrid Thank you but I don't think that works for me. Just to be clear, I have workbook with several worksheets. One is a list of many companies with their names, addresses, websites, ranks, sizes, etc. Then a sheet of all people with their contact info, titles, etc. And another sheet for all projects (past and current) of each company which each row includes project name, location, main contact, size, etc. similar to all other worksheets. What is in common in all of these sheets in the workbook is the Company name from the company sheet. Which I am using to relate all these sheets (i.e. Unique Key or ),...... Hope it makes sense. Now what I would like to achieve is by selecting each individual company name to be able to see all of its employees records and all projects which belongs to the company in question.

@YosifDakur Жыл бұрын

can you use the unique function inside an empty table? --> I have a filled out table and i need unique from column1 to be in table (for the rest of the columns i use XLOOKUP based on the unique values)

@ExcelOffTheGrid Жыл бұрын

Unfortunately not. Tables and dynamic arrays both have auto expansion/calculation features. As a result, we can't use a dynamic array inside a Table, because the two features conflict with each other. If the dynamic array returns a single value to a Table cell, then it works fine. But that won't work in your case.

@YosifDakur Жыл бұрын

@@ExcelOffTheGrid thank you for the quick reply

@brianmller5215 3 жыл бұрын

Will it be available in Excel 2021?

@ExcelOffTheGrid 3 жыл бұрын

I'm guessing so, but it's a bit early to know. So, at this point, your guess is as good as mine.

@undrawingend 2 жыл бұрын

Is there any other formula that’s like this ? As don’t have 365

@ExcelOffTheGrid 2 жыл бұрын

Check out this post: exceloffthegrid.com/list-unique-or-duplicate-values-with-excel-formulas/ It’s not an easy formula, but it should work.

@cathyvenn3306 Жыл бұрын

I am trying to do a unique list but is not working, is there someone who could help me

@user-qg4si2wv1l Ай бұрын

Hi! Example #5 Do not working!

@josephavelino2088 Жыл бұрын

Why it didn't work on my excel

@ExcelOffTheGrid Жыл бұрын

I’m guessing it’s because you’re probably on Excel 2019 or before.

@garciarogerio6327 2 жыл бұрын

Each day I realize even more how little I know Excel 😁😂

@ExcelOffTheGrid 2 жыл бұрын

Me too. There is too much for any one person to know. But watching KZfaq videos and reading blogs will certainly help you to find out what's worth learning.

@777kiya 2 жыл бұрын

I learned two things Magical

@ExcelOffTheGrid 2 жыл бұрын

Glad it was useful 👍

@sanjayverma9836 2 жыл бұрын

Column-A - column-B Sanjay. - 1st jan Rabi. - 1st jan Sanjay. - 2nd jan Rabi. - 1st jan Result should be - sanjay count - 2 - Rabi count - 1 How to unique these type data

@ankursharma6157 Жыл бұрын

Hi Sanjay Verma, नीचे दिए गए formulas के लिए Office 365 का subscription होना चाहिए या Excel 2021 version होना चाहिए. Range A1:A4 में नाम हैं. Range B1:B4 में dates हैं. Cell D1 में formula लिखिए: =UNIQUE(A1:A4) Cell E1 में formula लिखिए: =COUNTA(UNIQUE(FILTER(B1:B4,A1:A4=D1))) Formula को नीचे drag कीजिए. Thank You!