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.
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.👍👍😍😍
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.
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!😊👍
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.
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
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.
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
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!
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.
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 For Excel: Function FLATTEN(r As Range) As Variant Dim A As Variant m = r.Count ReDim A(1 To m, 0) i = 1 For Each c In r If CStr(c.Value) = "" Then A(i, 0) = "" Else A(i, 0) = c.Value End If i = i + 1 Next c FLATTEN = A End Function
Would prefer below then (no sorting tho) Function Flatt_JEC(rng As Range) For Each cl In rng If cl "" Then c00 = c00 & "~" & cl Next Flatt_JEC = Application.Transpose(Split(Mid(c00, 2), "~")) End Function
@@calvinlong1321 I don't know yet. But without VBA it is possible with Powerquery 1. Select range and transform to table (Ctrl+T) 1. Load table to Powerquery editor with Data/From sheet option 2. Select all columns and use "Unpivot columns" 3. Remove Attribute column 4. "Remove duplicates" 5. Close and load to excel
This was a great video. But one question. How would one filter a single column of empty cells using the FILTER formula? My thought was as follows. =FILTER(G7:G41,""). I would love some help with this, let me know where I'm wrong.
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.
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.
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 ;)
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
Get access to the complete Google Sheets Masterclass here 👉 www.xelplus.com/course/google-sheets/
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 :)
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.
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 😊
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!
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.
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!
She is one of the best Excel teachers on the Internet.
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!
Great tip, echoing everyone else, hoping Excel adds this functionality
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!
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?
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.
You are a gem! Thanks for this clear-to-understand tutorial.
Glad it was helpful!
Can you please suggest the easiest way to do this in O365 if possible.
Thank you! Does this work in Excel too?
Thank you for sharing. This simple tutorial will help me with my projects.
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 for this video. figured out a function ive been trying to get done on a sheet for the last 3 days
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.
Lovely teaching. Thanks
Glad you enjoyed it!
Ma'am.
Thank you so much...
Your videos really helpful for me.
Thanks a lot, I spent 2 hours in doing what you have done in less than a minute, :)
Leila : You are explaining things very clearly
Is there anything similar in Excel instead of Google sheets?
Thank you so much for all your advices
Another informative video. Thank you.
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
Good point, I never considered using Flatten with unique. Great tip
Thank you Leila, that was really helpful!
awesome way of teaching
Almost always learn something new with your videos. Well presented and pitched, thank you
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/
Thank you!
I would like to say you I love you you made my day!!! Thank you!
Glad I could help!
Amazing tip n well explained. thank you very much. 🙂
Extremely fruitful (info) as always. Thanks.
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!
Wow 👏 Each time, you just simplify our life! Thank you!
Just what I was looking for, thank you.
Thank you so much. What you suggest Excel or Google sheets. Please advise
Hi Leila ,
Thank you for videos.
I would appreciate if make videos on VBA excel and SQL Server..
You're a life saver madam! Thank you so much for this, it is EXACTLY what I was struggling with. ♥
Useful function many thanks Leila
Awesome Leila!
Thank you! 😄
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.
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
Great Ma'am 👍, Very informative
Thank you. This video is a life saver!
gracias por tu sabiduría
Awesome, I’ve been joining arrays
how?
@@TIMUAU curly braces and commas,semicolons
I am flattened.... There should be option to like it multiple times 👍👍👍👍
Thank You Mam 🙏🙏
AWESOME! I was having a really hard time transforming an array into a single column and filtering
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, ””) “
I'm using office 365, primarily excel, creating dynamic queries with msqry. Looking into Power query due to enhanced complexity
You are just amazing. I continue to learn a lot!
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)
Thank You Very Much !
Awesome... As always 👍🏻
Thank you for this useful video but I want to ask if there is a formula in excel which do the same thing
You are a gift. Thanks, Mam 😘
Your knowledge is amazing. This video solved a vexing problem I had that no one came close to providing the right formulas.
Leila, I love it
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?
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.
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.
I'm flattered 😊
😊
Thank you, which would be the similar of Flatten in Excel?, is there one?
Do we have any equivalent formula in Excel? How the same can be achieved in Excel with formulas?
Holy, you saved my day!
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 For Excel:
Function FLATTEN(r As Range) As Variant
Dim A As Variant
m = r.Count
ReDim A(1 To m, 0)
i = 1
For Each c In r
If CStr(c.Value) = "" Then
A(i, 0) = ""
Else
A(i, 0) = c.Value
End If
i = i + 1
Next c
FLATTEN = A
End Function
Only works with office 365(has spill) ;)
Would prefer below then (no sorting tho)
Function Flatt_JEC(rng As Range)
For Each cl In rng
If cl "" Then c00 = c00 & "~" & cl
Next
Flatt_JEC = Application.Transpose(Split(Mid(c00, 2), "~"))
End Function
Can you do this with LAMBDA Function without VBA ?
@@jecvman Great! It should be noted that it converts everything to text, but I like it.
@@calvinlong1321 I don't know yet. But without VBA it is possible with Powerquery
1. Select range and transform to table (Ctrl+T)
1. Load table to Powerquery editor with Data/From sheet option
2. Select all columns and use "Unpivot columns"
3. Remove Attribute column
4. "Remove duplicates"
5. Close and load to excel
Thank you very much, my dear teacher.
This was a great video. But one question. How would one filter a single column of empty cells using the FILTER formula? My thought was as follows. =FILTER(G7:G41,""). I would love some help with this, let me know where I'm wrong.
Interesting. I had never noticed or used the other parameters for the unique function!
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.
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.
Wowowo awesome trick 👍👍👍👌👌😊
Great video mam
Hi Leila,
Please explain the same function how to use in Desktop 365 Excel software
Regards
really nice...thanks!
Nice Leila.
Can we apply condional formatting - duplicate values ?
Another great tutorial -
FLATTEN does the job!!
As usual absolute extraordinary nice new function, but too new for my company's setup.
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.
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 like simplicity and it's a great help.
Hi Lella.. Is there a formula to get the execution result for the same sheet that is updated daily on date basis..?
Google sheets have some great functions. I wish Excel had more of these
So do I :)
OMG I just run into this problem TODAY. And I was wondering, does L has an answer to this 😄😄😄
Thanks. Pl do a simple way to get the last data of any column.
YEAH!!!!! Thank You!
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 ??
Nice one.
pls upload a video on more features in latest version of windows 10
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