How to Get a UNIQUE List from Many Columns Using FLATTEN in Google Sheets
Вставка
- Опубліковано 26 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Getting a Unique List from values in multiple columns can actually be quite hard. An easy solution is a hidden function in Google Sheets: The FLATTEN Function. It takes the values in a specified range and puts them in a single column. In this video you'll learn how to use it in combination with the UNIQUE function to get a distinct list from a range. This would be a great function to have in Microsoft Excel!
★★★ Join My Google Sheets Comprehensive Masterclass: ► www.xelplus.com/course/google...
00:00 How to Get Distinct Values from a Range of Values
00:27 How to Use the UNIQUE Function
02:17 How to Use the FLATTEN Function in Sheets
04:52 Wrap Up
LINKS to related videos:
► 10 Google Sheets Tips You DON'T Want to Miss: • 10 Google Sheets Tips ...
► QUERY Function in Sheets: • Google Sheets Query Fu...
Check out the complete blog post: www.xelplus.com/sheets-unique...
★★★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#googlesheets
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.
Almost always learn something new with your videos. Well presented and pitched, thank you
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 😊
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.
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 :)
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.
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.👍👍😍😍
It's my pleasure! I'm happy it was helpful.
Your knowledge is amazing. This video solved a vexing problem I had that no one came close to providing the right formulas.
Thank you!
Extremely fruitful (info) as always. Thanks.
Great tip, echoing everyone else, hoping Excel adds this functionality
Amazing!!! Thank you for this. You're the best!
Another informative video. Thank you.
Just what I was looking for, thank you.
She is one of the best Excel teachers on the Internet.
You are just amazing. I continue to learn a lot!
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!
Thanks a lot, I spent 2 hours in doing what you have done in less than a minute, :)
Thank you Leila, that was really helpful!
Good point, I never considered using Flatten with unique. Great tip
Ma'am.
Thank you so much...
Your videos really helpful for me.
Thank you. This video is a life saver!
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!
Amazing tip n well explained. thank you very much. 🙂
AWESOME! I was having a really hard time transforming an array into a single column and filtering
Awesome... As always 👍🏻
really nice...thanks!
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!
Leila : You are explaining things very clearly
Thank You Very Much !
Useful function many thanks Leila
She’s a phenomenal instructor!
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 very much, my dear teacher.
She never failed me to say "WOW"👍
You are a gem! Thanks for this clear-to-understand tutorial.
Glad it was helpful!
Another great tutorial -
Great Ma'am 👍, Very informative
YEAH!!!!! Thank You!
Can you please suggest the easiest way to do this in O365 if possible.
awesome way of teaching
gracias por tu sabiduría
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 😊
I like simplicity and it's a great help.
Interesting. I had never noticed or used the other parameters for the unique function!
Nice one.
Thanks for the tutorail. It's been really helpful. Any way to use this in pivot table?
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!
Holy, you saved my day!
Awesome Leila!
Thank you! 😄
Thank you So much Big Problem simple solutions
Great video mam
Leila, I love it
You are a gift. Thanks, Mam 😘
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!
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
Wowowo awesome trick 👍👍👍👌👌😊
Thank you! Does this work in Excel too?
Thank you
SWEET!
Obrigado 👏👏👏👏👏👏👏
Nice Leila.
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'm flattered 😊
😊
I would like to say you I love you you made my day!!! Thank you!
Glad I could help!
Thank You Mam 🙏🙏
Hi Leila ,
Thank you for videos.
I would appreciate if make videos on VBA excel and SQL Server..
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.
You're a life saver madam! Thank you so much for this, it is EXACTLY what I was struggling with. ♥
As usual absolute extraordinary nice new function, but too new for my company's setup.
Thanks
Awesome, I’ve been joining arrays
how?
@@TIMUAU curly braces and commas,semicolons
Is there anything similar in Excel instead of Google sheets?
Thank you so much for all your advices
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, which would be the similar of Flatten in Excel?, is there one?
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? :)
Great
FLATTEN does the job!!
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. What you suggest Excel or Google sheets. Please advise
Leila you rule!
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.
I am flattened.... There should be option to like it multiple times 👍👍👍👍
Can we apply condional formatting - duplicate values ?
Hi Lella.. Is there a formula to get the execution result for the same sheet that is updated daily on date basis..?
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/
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.
Do we have any equivalent formula in Excel? How the same can be achieved in Excel with formulas?
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
Google sheets have some great functions. I wish Excel had more of these
So do I :)
makasih
Thanks. Pl do a simple way to get the last data of any column.
I'm using office 365, primarily excel, creating dynamic queries with msqry. Looking into Power query due to enhanced complexity
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
عيد مبارك
Blessed Eid
Eid Mubarak
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.
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