How to Get a UNIQUE List from Many Columns Using FLATTEN in Google Sheets

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!

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:

🚩Let’s connect on social:

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

[operlandsen]

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("<a><b>"&SUBSTITUTE(TEXTJOIN("|", TRUE, InputRange), "|", "</b><b>")&"</b></a>", "//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.

[stratman]

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

[OwenGeeAndre]

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 😊

[cenevspeed]

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

[smkybare]

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

[asish_apr]

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

[samjain]

Hi Leila,

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

[jeremytan]

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

[nitesh.]

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

[HowToAnalyst]

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

[NachoTeso]

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.

[vijayarjunwadkar]

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!😊👍

[asmrindia]

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 😊

[mrongad]

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

[ash_m_]

Thank you! Does this work in Excel too?

[dragonshadow]

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.

[dicksongan]

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!

[vidhyashankarkannan]

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.

[cenevspeed]

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

[skmaheswaran]

Leila : You are explaining things very clearly