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 😊
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
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
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.
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? :)
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 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.
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
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!
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.
MADAM SUPPOSE i want to filter such data which is common in all column for example Baden is common in all columns what will it's formula. Or can you send video link if it's uploaded in channel???
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, ””) “
