UNIQUE function in Excel (with 6 examples) | Remove duplicates with a function | Excel Off The Grid
Vložit
- čas přidán 7. 07. 2024
- ★ 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
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.
Thanks Dale. Working through examples is the best way to learn. 👍👍👍
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!
Yes, UNIQUE is pretty cool. Glad I could help. More videos coming soon :-)
Clearest explanations I’ve found. Great work
Thanks, that’s very kind of you to say. 😀
Precise and articulate. Thank you very much for sharing your talents.
Thank you 👍
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 !! :-)
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.
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.
Thank you, I appreciate the feedback and support 😀
great content on Microsoft excel
this is by far, one of the most helpful videos i've seen. thank u so much.
Thank You 😀
Perfect. No more words. Thank you for you explanation.
You’re welcome.
You are the true master in excel DA.. Want more excel topics from you..
More videos are coming - I'm just working on another project at the moment. Once that's done, there will be more.
@@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...
Well presented. Thank you for the practice worksheets.
You are welcome!
This is epic.. the examples were mind blowing ❤️
Thanks - 👍
Thank you so much sir, great and simple explaination 🌸🌸
Very instructive videos! Thanks and keep on sharing please.
There will be more coming soon 👍
Liked and subscribed! Thank you!
Awesome, thank you!
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.
Glad it was helpful! Welcome aboard.
Example 6. Nice.
Very informative video, Learn lots of new stuff from this.
Thank you. Glad to hear it!
so helpful , Thank you !
Thank You 😀
Bro, that was awesome! Thanks!!
Glad you liked it! 😀
You're awesome! Thanks for the video
You’re welcome.
Great video, thank you
You are welcome!
Thanks a lot Man!
You’re welcome ✅
Im from india great teacher. Great. Thank u so much .
Your welcome, Thank you :-)
thanks
this very complete to learn
because sample file and user guide already
nice and excellent
bravo
thank you very much
Thank you so much, that is very kind of you to say 😀
Thank you :)
No problem 😀
Thanks a lot
You’re welcome.
Thank you for sharing this knowledge. Continue being awesome.
I’ll try 😀
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).
Very good, I haven't seen the # trick before
The # is part of the new Excel calculation engine. Which is currently only for Excel 365 subscribers.
Check out this video for more information:
czcams.com/video/TCwec8-tsDk/video.html
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?
It can, but it's not easy.
Check out this video from Mike Girvin: czcams.com/video/4qZ8ud8akus/video.html
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?
You need the FILTER function nested inside UNIQUE.
I’ve got a video on FILTER too, check it out.
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?
Too many.
And you never need to do that again 😁
Great video! Have you tried using the UNIQUE formula when your list has blanks? Have you figured out how to exclude those?
You can combine the UNIQUE function with the FILTER function. Something like the following would work:
=UNIQUE(FILTER(A2:A11,A2:A11""))
@@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
@@ExcelOffTheGrid very helpful
How to select unique names from many columns (per range), e.g. we want unique names in the rangeA1;K30?
I think you would need the new TOCOL function which is available in Excel 365.
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.
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. 🙂
I think you are probably looking for the SUBSTITUTE function. It can find the 2nd instance of an item and replace it with blank.
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?
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.
Does unique fx work in excel 2013 ? If not what else can use directly, apart from pivot table?
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
Hi and thanks for the video. The unique function spills when used in a table. How do we prevent that
As on 13-June-2024, You cannot use a formula inside a Table . . the result of which spills.
Awesome video, really helpful! But how did you put that line beneath the words? Hahaha, I'd like to know.
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. 😀
@@ExcelOffTheGrid Oh, it's exactly that, dude. Hahaha, thank you a lot. I really forgot that completely, I thought it was an Excel border.
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.
I learned two things
Magical
Glad it was useful 👍
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.
Hi Geoffrey,
Very Interesting Q.
My attempt:
✓ Dataset Range: B3:F250.
- SL. NO.
- TRAINING DATE
- NAME
- COMPANY
- TRAINING
✓ Formula
=LET(a, UNIQUE(HSTACK(D4:D250, F4:F250)), b, TAKE(a, , 1), c, TAKE(a, , -1), d, MAP(b, c, LAMBDA(z,y, MAX(FILTER(C4:C250, (D4:D250 = z) * (F4:F250 = y))))), SORT(HSTACK(b, c, d), {1,2}))
✓ What this formula does:
- For the unique combination of both Name & Training . . this formula extracts the Name, Training and the latest Training Date.
Hope this helps.
@@ankursharma6157 Hi Ankur, this is expert level. You have used functions and syntax I have never seen before so thank you. I have some reading and thinking to do!
4:38
「TRUE」可以換成『1』嗎？
Is thus formula avilable in 2019?? Coz i have the updated version but i can't see this formula can you help on this ???
Sorry, it's currently only available on Excel 365.
Is there any other formula that’s like this ? As don’t have 365
Check out this post: exceloffthegrid.com/list-unique-or-duplicate-values-with-excel-formulas/
It’s not an easy formula, but it should work.
Great video!
but what if there is a blank in between cells?
Then you get a zero included in the spill array.
You can use the FILTER function to remove the zero.
@@ExcelOffTheGrid thank you so much!
I am trying to do a unique list but is not working, is there someone who could help me
Each day I realize even more how little I know Excel 😁😂
Me too. There is too much for any one person to know.
But watching CZcams videos and reading blogs will certainly help you to find out what's worth learning.
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)
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.
@@ExcelOffTheGrid thank you for the quick reply
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.
Sound to me that you need the FILTER function:
exceloffthegrid.com/filter-function-in-excel/
@@ExcelOffTheGrid I am using Excel 2019, the video says the Filter function is only available for 365?
In which case FILTER isn't going to work for you.
Check out this video as an approach that should work:
czcams.com/video/fDB1Ktyhp3Y/video.html
@@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.
Will it be available in Excel 2021?
I'm guessing so, but it's a bit early to know. So, at this point, your guess is as good as mine.
Hi! Example #5 Do not working!
Why it didn't work on my excel
I’m guessing it’s because you’re probably on Excel 2019 or before.
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
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!