No video :(

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

Подписаться 29 тыс.

Просмотров 77 тыс.

50% 1

★ 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

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 114

@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. 👍👍👍

@Hossana6 Месяц назад

great content on Microsoft excel

@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 :-)

@henrytran4782 3 года назад

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

@ExcelOffTheGrid 3 года назад

Thank you 👍

@taymaateyah1757 8 месяцев назад

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

@608er Год назад

Clearest explanations I’ve found. Great work

@ExcelOffTheGrid Год назад

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

@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 😀

@spiritualbeingshavinghuman6356 2 года назад

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

@ExcelOffTheGrid 2 года назад

Thank You 😀

@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...

@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.

@juansebastianbuitrago6654 2 года назад

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

@ExcelOffTheGrid 2 года назад

You’re welcome.

@roberth.9558 Год назад

Well presented. Thank you for the practice worksheets.

@ExcelOffTheGrid Год назад

You are welcome!

@SamiElzaim 2 года назад

Very instructive videos! Thanks and keep on sharing please.

@ExcelOffTheGrid 2 года назад

There will be more coming soon 👍

@mdmueenpasha1637 3 года назад

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

@ExcelOffTheGrid 3 года назад

Thanks - 👍

2 месяца назад

Example 6. Nice.

@DROIDFARM Год назад

Liked and subscribed! Thank you!

@ExcelOffTheGrid Год назад

Awesome, thank you!

@raghadshahir9966 Год назад

so helpful , Thank you !

@ExcelOffTheGrid Год назад

Thank You 😀

@AmitKasarLive 3 года назад

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

@ExcelOffTheGrid 3 года назад

Thank you. Glad to hear it!

@Foreman1329 3 года назад

Great video, thank you

@ExcelOffTheGrid 3 года назад

You are welcome!

@josuezuniga5347 Год назад

You're awesome! Thanks for the video

@ExcelOffTheGrid Год назад

You’re welcome.

@grandpasaysrelax175 2 года назад

Thanks a lot Man!

@ExcelOffTheGrid 2 года назад

You’re welcome ✅

@MrSupernova111 Год назад

Bro, that was awesome! Thanks!!

@ExcelOffTheGrid Год назад

Glad you liked it! 😀

@nicoleownby2329 Год назад

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).

@Melki 3 года назад

Thank you :)

@ExcelOffTheGrid 3 года назад

No problem 😀

@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 😀

@rameshhariharan2623 2 года назад

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

@ExcelOffTheGrid 2 года назад

Your welcome, Thank you :-)

@abdulrahmanmuslih141 Год назад

Thanks a lot

@ExcelOffTheGrid Год назад

You’re welcome.

@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.

@MGHxxvi 3 года назад

Thank you for sharing this knowledge. Continue being awesome.

@ExcelOffTheGrid 3 года назад

I’ll try 😀

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TCwec8-tsDk.html

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-4qZ8ud8akus.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.

@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

@level2456 2 месяца назад

Hi and thanks for the video. The unique function spills when used in a table. How do we prevent that

@ankursharma6157 Месяц назад

As on 13-June-2024, You cannot use a formula inside a Table . . the result of which spills.

@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.

@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

@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.

@zhangren1988 2 года назад

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

@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.

@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 😁

@brap5829 4 месяца назад

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.

@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!

@GeoffreyStewart 5 месяцев назад

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.

@ankursharma6157 Месяц назад

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.

@GeoffreyStewart Месяц назад

@@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!

@777kiya 2 года назад

I learned two things Magical

@ExcelOffTheGrid 2 года назад

Glad it was useful 👍

@cathyvenn3306 2 года назад

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

@user-fy4yd9tz4w 7 месяцев назад

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.

@nikhilsoni1177 3 года назад

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 3 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fDB1Ktyhp3Y.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.

@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.

@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 RU-vid videos and reading blogs will certainly help you to find out what's worth learning.

@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.

@user-qg4si2wv1l 4 месяца назад

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.

@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!