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.👍👍😍😍
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.
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 😊
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.
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.
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.
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!😊👍
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.
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
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? :)
quick question ;) if i have several numbers in a cell ;) how i can count them? let's say i have such a string in a text - 1,23,33,45 (4 numbers). is there a formula to count? cuz it's been counted as 1, as they are in one cell ;) thank you ;)
Greetings from my heartcore. Nice to have such video. Please let me know how I can do the same in excel file. Looking forward to hearing from you. Thanks in advance.
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.
hello, first, thank you for your helpful video. my question about FLATTEN Function is not available any more, could you please give any solution to have my data in one column.
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
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) < a(i) Then y = a(i) a(i) = a(j) a(j) = y End If Next Next Flat_sort = Application.Transpose(a) End Function
Unique values, flattened in Excel(365 only): Function Unique_Flat(rng As Range) ar = rng With CreateObject("Scripting.dictionary") For i = 1 To UBound(ar) For j = 1 To UBound(ar, 2) If ar(i, j) "" Then c00 = .Item(ar(i, j)) Next Next Unique_Flat = Application.Transpose(.keys) End With End Function
I am curious as to how Google is able to use these new functions which are in Excel in their own product (for example: FILTER, UNIQUE and SORT). I would have thought that Microsoft would have gotten a patent/copyright on them and that Google would get sued. Any info regarding the legal circumstances surrounding this? Thanks.
This is really good Leila. I have a question: Why did you have "Flatten" twice in the formula? =sort(unique(filter(Flatten(B2:B12,D2:D12),Flatten(B2:B12,D2:D12)" ")),1,False)
First flatten is combine data to single column. Second flatten use as a condition for filter function…. “I want only data in this column without blank value (not equal to blank, ””) “
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?
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.
@@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!
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.
Mam, thanks for helping us.. i have one more doubt same like this, do we have formula to consolidate 2 different columns into one in microsoft excel? i am currently creating 2 tables and appending both ... pls need ur guidance .. it would be great ful to u.. pls help
![BOWKYLION - วิงวอน (ex-change) [Official MV]](http://i.ytimg.com/vi/cLdnZWDaO-w/mqdefault.jpg)
Get access to the complete Google Sheets Masterclass here 👉 www.xelplus.com/course/google-sheets/
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.👍👍😍😍
It's my pleasure! I'm happy it was helpful.
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.
Thanks for sharing, Ole!
I love you Ole !!! It works on my workbook!
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 😃
Thats amazing!
works a charm, even where you have multiple ranges on different sheets! Thanks :)
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 😊
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.
Hi Leila,
I have been stuck with this issue for 3 hours but your video has helped me to solve it quickly. Thanks!
Glad it helped!
Thank You So Much Mam!! I understood everything so clearly 🔥🔥 please continue making such astonishing tutorials!! Thanks
Thank you, I will.
@@LeilaGharani Have we got a flatten equivalent in excel yet!
She is one of the best Excel teachers on the Internet.
Great tip, echoing everyone else, hoping Excel adds this functionality
thank you for this video. figured out a function ive been trying to get done on a sheet for the last 3 days
Leila, you are the magician of EXCEL. Every time you post a video I am finding some surprising tricks. Thank you so much.
Happy to hear that!
Thank you so much for this tutorial. I realised there is no flatten function in Excel. Is there an equivalent function for Excel?
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.
@@edwardburns220 I don't have vstack yet. I'm on office 365. Do you know any alternatives?
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.
Thank you! Does this work in Excel too?
Thank you for sharing. This simple tutorial will help me with my projects.
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!😊👍
You can do this in Excel using Power Query. Append all columns and simple remove duplicates in PQ editor.
@@chinmaya.6934 Thanks Chinmay!
Thanks a lot, I spent 2 hours in doing what you have done in less than a minute, :)
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.
Can you please suggest the easiest way to do this in O365 if possible.
You are a gem! Thanks for this clear-to-understand tutorial.
Glad it was helpful!
Ma'am.
Thank you so much...
Your videos really helpful for me.
Leila : You are explaining things very clearly
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.
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 😊
Good point, I never considered using Flatten with unique. Great tip
Just what I was looking for, thank you.
Thank you Leila, that was really helpful!
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
She’s a phenomenal instructor!
awesome way of teaching
Another informative video. Thank you.
Amazing tip n well explained. thank you very much. 🙂
Wow 👏 Each time, you just simplify our life! Thank you!
Is there anything similar in Excel instead of Google sheets?
Thank you so much for all your advices
I am flattened.... There should be option to like it multiple times 👍👍👍👍
Thank you!
Thank you. This video is a life saver!
Thank you So much Big Problem simple solutions
Could you please tell me which editing software you use??
This is Google sheets, it is not an editing software, I don't think
@@mlittlemlittle2966 I wonder if they meant which video editing software.
@@gmscott9319 oh. Makes more since. I was misled by the phrasing regarding the spirit of the question
Mostly Camtasia.
@@LeilaGharani thanks for telling
I would like to say you I love you you made my day!!! Thank you!
Glad I could help!
gracias por tu sabiduría
AWESOME! I was having a really hard time transforming an array into a single column and filtering
Hi Leila ,
Thank you for videos.
I would appreciate if make videos on VBA excel and SQL Server..
Holy, you saved my day!
Almost always learn something new with your videos. Well presented and pitched, thank you
Useful function many thanks Leila
Extremely fruitful (info) as always. Thanks.
Awesome, I’ve been joining arrays
how?
@@TIMUAU curly braces and commas,semicolons
Thank You Mam 🙏🙏
Thank You Very Much !
FLATTEN does the job!!
Interesting. I had never noticed or used the other parameters for the unique function!
You're a life saver madam! Thank you so much for this, it is EXACTLY what I was struggling with. ♥
You are a gift. Thanks, Mam 😘
Great "show". It was VERY useful to me - AND I love your mug. Can I buy it somewhere?
Glad you like it! You can get our merch here: xelplus.creator-spring.com/
Your knowledge is amazing. This video solved a vexing problem I had that no one came close to providing the right formulas.
I'm using office 365, primarily excel, creating dynamic queries with msqry. Looking into Power query due to enhanced complexity
Great Ma'am 👍, Very informative
Thank you very much, my dear teacher.
Awesome Leila!
Thank you! 😄
You are just amazing. I continue to learn a lot!
As usual absolute extraordinary nice new function, but too new for my company's setup.
Thank you so much. What you suggest Excel or Google sheets. Please advise
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? :)
Thank you for this useful video but I want to ask if there is a formula in excel which do the same thing
quick question ;) if i have several numbers in a cell ;) how i can count them? let's say i have such a string in a text - 1,23,33,45 (4 numbers). is there a formula to count? cuz it's been counted as 1, as they are in one cell ;) thank you ;)
I'm flattered 😊
😊
Leila, I love it
Thanks for the tutorail. It's been really helpful. Any way to use this in pivot table?
I like simplicity and it's a great help.
Greetings from my heartcore. Nice to have such video. Please let me know how I can do the same in excel file. Looking forward to hearing from you. Thanks in advance.
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.
hello,
first, thank you for your helpful video.
my question about FLATTEN Function is not available any more, could you please give any solution to have my data in one column.
Do we have any equivalent formula in Excel? How the same can be achieved in Excel with formulas?
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)
YEAH!!!!! Thank You!
Another great tutorial -
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
Hi, what is the reference called in the vba library?
@@BasBruijnis Maybe you don't need it. Try to find: mscorlib.dll
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) < a(i) Then
y = a(i)
a(i) = a(j)
a(j) = y
End If
Next
Next
Flat_sort = Application.Transpose(a)
End Function
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 ??
OMG I just run into this problem TODAY. And I was wondering, does L has an answer to this 😄😄😄
Thank you, which would be the similar of Flatten in Excel?, is there one?
Unique values, flattened in Excel(365 only):
Function Unique_Flat(rng As Range)
ar = rng
With CreateObject("Scripting.dictionary")
For i = 1 To UBound(ar)
For j = 1 To UBound(ar, 2)
If ar(i, j) "" Then c00 = .Item(ar(i, j))
Next
Next
Unique_Flat = Application.Transpose(.keys)
End With
End Function
I am curious as to how Google is able to use these new functions which are in Excel in their own product (for example: FILTER, UNIQUE and SORT). I would have thought that Microsoft would have gotten a patent/copyright on them and that Google would get sued.
Any info regarding the legal circumstances surrounding this?
Thanks.
Hi Leila,
Please explain the same function how to use in Desktop 365 Excel software
Regards
Is this works in multiple rows ina addition to multiple sheets?
Could you make a tutorial of this?
unique list from multiple rows and sheets..❤
Awesome... As always 👍🏻
Wowowo awesome trick 👍👍👍👌👌😊
Can we make a unique table out of it. Pla help
This is really good Leila. I have a question: Why did you have "Flatten" twice in the formula? =sort(unique(filter(Flatten(B2:B12,D2:D12),Flatten(B2:B12,D2:D12)" ")),1,False)
First flatten is combine data to single column. Second flatten use as a condition for filter function…. “I want only data in this column without blank value (not equal to blank, ””) “
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?
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.
@@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!
@socialgamer23
You can also use =UNIQUE(TOCOL(array of data)
Thanks all! I used VSTACK.
Nice Leila.
Google sheets have some great functions. I wish Excel had more of these
So do I :)
Hi Lella.. Is there a formula to get the execution result for the same sheet that is updated daily on date basis..?
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.
really nice...thanks!
pls upload a video on more features in latest version of windows 10
Hi. I need to filter a pivot table based on unique values of a particular columns data in google sheets. Can you help?
Thanks. Pl do a simple way to get the last data of any column.
She never failed me to say "WOW"👍
Thank you
Mam, thanks for helping us.. i have one more doubt same like this, do we have formula to consolidate 2 different columns into one in microsoft excel? i am currently creating 2 tables and appending both ... pls need ur guidance .. it would be great ful to u.. pls help
Maybe Power Query can help?