Get access to the complete Google Sheets Masterclass here 👉 www.xelplus.com/course/google-sheets/

Always enjoy these videos showing some of the uncommon functions in excel and how they can be used. I know there have been a few times were I could have used this and did it the long way of copying each column of data into 1 column then using the remove duplicate button.

Thank you Ms. Leila for this video tutorial. It is the small things that add up in the end to make a workable solution. And you help me every day.

Great tip, echoing everyone else, hoping Excel adds this functionality

Almost always learn something new with your videos. Well presented and pitched, thank you

Your knowledge is amazing. This video solved a vexing problem I had that no one came close to providing the right formulas.

Yes! Thank you, I needed something like this. I was using Unique 2 times for something similar (first to just wrap all the names in one different sheet, then choosing each individual column to return the names in a single column in that same sheet). This will be faster and better.

Thank you for sharing. This simple tutorial will help me with my projects.

This formula will return a similar result in Excel (with or without the SORT function) and should be reasonably short and simple: =SORT(UNIQUE(FILTERXML(""&SUBSTITUTE(TEXTJOIN("|",TRUE,InputRange),"|","")&"","//b"))) Replace InputRange with the cell range you want to use as input. Could also be wrapped in a LAMBDA function for a more user friendly version.

@LeilaGharani

2 жыл бұрын

Thanks for sharing, Ole!

@leonardoalvespereira9494

2 жыл бұрын

I love you Ole !!! It works on my workbook!

@IbrahimNgouhouo

2 жыл бұрын

Thanks Ole I don't understand some functions here. I guess i'll just save it somewhere and copy and paste each time I need to use it 😃

@MrHonda52

2 жыл бұрын

Thats amazing!

@tobydavy2052

2 жыл бұрын

works a charm, even where you have multiple ranges on different sheets! Thanks :)

Wow 👏 Each time, you just simplify our life! Thank you!

Once again - Thank you Leila. I have tried for 2 days to figure how to achieve in excel what =UNIQUE(FLATTEN(XX:XXXX)) does in sheets. I have your Udemy PQ course and a couple of your others, searched high, low and in between. Watched hours of you tube vids, and the feeling when all of that gets solved in 2mins40 of your mellifluous voice simply cannot be described. Thanks just is not enough.👍👍😍😍

@LeilaGharani

Жыл бұрын

It's my pleasure! I'm happy it was helpful.

Extremely fruitful (info) as always. Thanks.

Thank You So Much Mam!! I understood everything so clearly 🔥🔥 please continue making such astonishing tutorials!! Thanks

@LeilaGharani

3 жыл бұрын

Thank you, I will.

@blacktalks256

2 жыл бұрын

​@@LeilaGharani Have we got a flatten equivalent in excel yet!

Leila, you are the magician of EXCEL. Every time you post a video I am finding some surprising tricks. Thank you so much.

@LeilaGharani

3 жыл бұрын

Happy to hear that!

Thanks a lot, I spent 2 hours in doing what you have done in less than a minute, :)

If I could use a Voice note, you'd have heard me crying tears of joy... As an entry level Data Analyst, this had been an issue for me for hours and I couldn't find a good solution... Thanks and I'm subscribed 😊

Amazing!!! Thank you for this. You're the best!

Another informative video. Thank you.

Hi Leila, I have been stuck with this issue for 3 hours but your video has helped me to solve it quickly. Thanks!

@LeilaGharani

Жыл бұрын

Glad it helped!

Just what I was looking for, thank you.

You are just amazing. I continue to learn a lot!

Leila : You are explaining things very clearly

Ma'am. Thank you so much... Your videos really helpful for me.

She’s a phenomenal instructor!

Good point, I never considered using Flatten with unique. Great tip

Thank you Leila, that was really helpful!

AWESOME! I was having a really hard time transforming an array into a single column and filtering

Thank you!

Amazing tip n well explained. thank you very much. 🙂

Thank you. This video is a life saver!

She is one of the best Excel teachers on the Internet.

You are a gem! Thanks for this clear-to-understand tutorial.

@LeilaGharani

7 ай бұрын

Glad it was helpful!

thank you for this video. figured out a function ive been trying to get done on a sheet for the last 3 days

Awesome... As always 👍🏻

Useful function many thanks Leila

Thank you very much, my dear teacher.

Great Ma'am 👍, Very informative

Thank You Very Much !

Another great tutorial -

really nice...thanks!

gracias por tu sabiduría

awesome way of teaching

Can you please suggest the easiest way to do this in O365 if possible.

Interesting. I had never noticed or used the other parameters for the unique function!

Wowowo awesome trick 👍👍👍👌👌😊

YEAH!!!!! Thank You!

You are a gift. Thanks, Mam 😘

I like simplicity and it's a great help.

Thank you So much Big Problem simple solutions

Thanks for the tutorail. It's been really helpful. Any way to use this in pivot table?

Just discover your channel. I just wanted to know how to create a QR code for my landing page business and your video pop up first. thank you for what you are doing

Thank You Mam 🙏🙏

She never failed me to say "WOW"👍

Holy, you saved my day!

Nice one.

FLATTENed by this trick, but appears that it is UNIQUE to Google Sheets! Wish we had it in Excel! Thanks Leila for sharing though, as we can bring the end results from Sheets to Excel anyway! Best of both!😊👍

@chinmaya.6934

3 жыл бұрын

You can do this in Excel using Power Query. Append all columns and simple remove duplicates in PQ editor.

@vijayarjunwadkar

3 жыл бұрын

@@chinmaya.6934 Thanks Chinmay!

Thank you! Does this work in Excel too?

Awesome Leila!

@LeilaGharani

3 жыл бұрын

Thank you! 😄

Osum Video Mam, Very Useful to me and all. Thanks Please make a detailed video on Which is better between M.S. Office and Google Sheets. Thanks 😊

Thank you so much for this tutorial. I realised there is no flatten function in Excel. Is there an equivalent function for Excel?

@edwardburns220

2 жыл бұрын

Hi! Use the =VSTACK formula in excel! Works pretty much the same as the flatten formula but you need to highlight each column and separate with a comma. Hope this helps.

@flashgenai

2 жыл бұрын

@@edwardburns220 I don't have vstack yet. I'm on office 365. Do you know any alternatives?

@vidhyashankarkannan4329

2 жыл бұрын

Hi, I had the same issue. So I copied my data into google sheets, used Flatten, copied the result back to O365 and then used the functions in Excel to get the unique list.

Great video mam

Thank you

You're a life saver madam! Thank you so much for this, it is EXACTLY what I was struggling with. ♥

I wish you could have heard me scream when you whipped out the FLATTEN function that fixed the exact problem I was having. Thanks for the awesome tutorial!

As usual absolute extraordinary nice new function, but too new for my company's setup.

Hi Leila , Thank you for videos. I would appreciate if make videos on VBA excel and SQL Server..

Was looking for a good way to compare two lists to see, what is only in one, both or the other. (I typically do it with Unix „comp“ command)

I would like to say you I love you you made my day!!! Thank you!

@LeilaGharani

2 жыл бұрын

Glad I could help!

SWEET!

Thank you, which would be the similar of Flatten in Excel?, is there one?

Obrigado 👏👏👏👏👏👏👏

Is there anything similar in Excel instead of Google sheets? Thank you so much for all your advices

Nice Leila.

Awesome, I’ve been joining arrays

@TIMUAU

2 жыл бұрын

how?

@geoschwa

2 жыл бұрын

@@TIMUAU curly braces and commas,semicolons

I'm flattered 😊

@LeilaGharani

3 жыл бұрын

😊

Thank you so much. What you suggest Excel or Google sheets. Please advise

Hi Lella.. Is there a formula to get the execution result for the same sheet that is updated daily on date basis..?

Leila, I love it

Thank you for this useful video but I want to ask if there is a formula in excel which do the same thing

Thank you so much for this amazing video, but I have an Excel sheet and using it for data collection and things I want from it which is really complicated and don't know how to get in touch with you and explain to you so that you can help me on that????. Is there any way to get in touch with you???. Thanks in advance.

Thanks

Leila you rule!

Hi Leila, Question: What is the easiest way to find the Intersection of multiple (as in more than 2) columns in Excel? The UNIQUE function covers the Union, but we are still behind on Intersection. I feel that there should be a special function to do this! And then we also need a set minus function (as in set A-B, which is different than B-A). Could you do a video on this? Thank you.

Can we apply condional formatting - duplicate values ?

Great "show". It was VERY useful to me - AND I love your mug. Can I buy it somewhere?

@LeilaGharani

7 ай бұрын

Glad you like it! You can get our merch here: xelplus.creator-spring.com/

Great

Google sheets have some great functions. I wish Excel had more of these

@LeilaGharani

3 жыл бұрын

So do I :)

Is there a way to copy (automatically) data from one spreadsheet to another spreadsheet (both spreadsheets are online on the web on a sharepoint)? If yes, could you please do a video on this, too? :)

FLATTEN does the job!!

If someone is interested in doing this within Excel: With the System.Collections.ArrayList you could do the same(inlcudes the sorting part). You probably have to enable this object in the VBA library first. Put some data in Columns A, B and C and run this macro Sub Flatten_VBA() arr = Sheets(1).Cells(1).CurrentRegion With CreateObject("System.Collections.Arraylist") For Each cl In arr If Not .Contains(cl) Then .Add cl Next .Sort '.Reverse for Descending sort order Sheets(1).Cells(1, 6).Resize(.Count) = Application.Transpose(.toarray) End With End Sub And ofcourse Power Query can do this too very easily

@BasBruijnis

3 жыл бұрын

Hi, what is the reference called in the vba library?

@jecvman

3 жыл бұрын

@@BasBruijnis Maybe you don't need it. Try to find: mscorlib.dll

@jecvman

3 жыл бұрын

Function without object: Function Flat_sort(rng As Range) ar = rng Dim a() For Each cl In ar If cl "" Then ReDim Preserve a(j) a(j) = cl j = j + 1 End If Next For i = 0 To UBound(a) For j = i + 1 To UBound(a) If a(j) y = a(i) a(i) = a(j) a(j) = y End If Next Next Flat_sort = Application.Transpose(a) End Function

Could you please tell me which editing software you use??

@mlittlemlittle2966

3 жыл бұрын

This is Google sheets, it is not an editing software, I don't think

@gmscott9319

3 жыл бұрын

@@mlittlemlittle2966 I wonder if they meant which video editing software.

@mlittlemlittle2966

3 жыл бұрын

@@gmscott9319 oh. Makes more since. I was misled by the phrasing regarding the spirit of the question

@LeilaGharani

3 жыл бұрын

Mostly Camtasia.

@cenevspeed

3 жыл бұрын

@@LeilaGharani thanks for telling

Do we have any equivalent formula in Excel? How the same can be achieved in Excel with formulas?

Hi Leila, Everyone does not have office 365 , I am using Excel 2013 as provided by my office. How can I sort this in that ??

First of all, Thank you so much. I have a question here: Once this Unique list is ready, can we use same values/column and apply VLOOKUP value here? For example: In this video, I use VLOOKUP value from column G. Thanks in advance.

I am flattened.... There should be option to like it multiple times 👍👍👍👍

I'm using office 365, primarily excel, creating dynamic queries with msqry. Looking into Power query due to enhanced complexity

If Horizontal array data and to be convert in one column from data one column after next column data. How it can be done with flatten command? Is there possible to use Flatten command in excel

Hi. I need to filter a pivot table based on unique values of a particular columns data in google sheets. Can you help?

Great tip! I don't know if arrayformulas didn't exist at this time, but you can recreate this for combinations of columns using =UNIQUE({range1;range2;range3}) which appends the ranges and then uniques them. I assume you could use arrays{} in this case as well in place of flatten?

@socialgamer23

7 ай бұрын

why doesn't it work for me? I am getting 'There is a problem with this formula error'. The formula used "=UNIQUE({Z3:Z6;Z3:Z7})" Can you help here? Coz my excel doesn't seem to have flatten either.

@t.wilson

6 ай бұрын

@@socialgamer23 ​ This is for google sheets. Excel requires a different solution to my knowledge due to the array argument. However, with your example, you do not need an array. You can use =UNIQUE(Z3:Z7) alone. Hope that helps!

@t.wilson

6 ай бұрын

@socialgamer23 You can also use =UNIQUE(TOCOL(array of data)

@socialgamer23

6 ай бұрын

Thanks all! I used VSTACK.

makasih