Excel Magic Trick 1313: Array Formula to Create Sorted Unique List in Cell. HUGE Formula. TEXTJOIN.

Download Excel Files:

Formula posted by Bill Szysz.
Lean how to create an Array Formula to Create Sorted Unique List in Cell. This is a huge complicated formula that requires the Excel 2016 function TEXTJOIN. There are many interesting formula elements in a single array formula to do the near impossible. Here are the topics covered (different parts that go into the final single cell formula):
1. (00:09) Problem Introduction
2. (01:43) Array of All Relative Positions & FALSEs for Unique List: using MATCH, IF and ROW functions. This is Formula Element #1.
3. (04:36) Unique Count: using previous formula element inside COUNT function. This is Formula Element #3.
4. (05:03) Array of Sequential Numbers for Unique Count: using ROW function, INDIRECT function and previous formula element. In chapter 7 of the Ctrl + Shift + Enter book, this formula element is called “Dynamic Variable-Length Array of Sequential Numbers”. Learn about the difference between using “1:” (just a row reference) and “A1:A” (a range of cells reference) (trick from Bill Szysz) in this formula element. This is Formula Element #3.
5. (06:58) Array of All Relative Positions: using SMALL function, Formula Element #1 and Formula Element #2. This is Formula Element #4.
6. (08:13) Unique List: using INDEX function, N function, IF function and Formula Element #5.
7. (09:57) Ranks (How Many Equal To or Above): using Formula Element #5, TRANSPOSE Function, Comparative Operator (to help sort), MMULT function (to add rows and convert 9 by 9 array into a single column array with the dimensions 9 by 1), Formula Element #3, and an exponent of zero (to convert all numbers to one). This is a trick I first learned from Domenic at the Mr Excel Message Board. This is Formula Element #6.
8. (16:08) Sort Relative Positions of Rank: using: MATCH function, Formula Element #3 and Formula Element #6. This is Formula Element #7.
9. (18:07) Sorted Unique List: using INDEX function, Formula Element #5 (Unique List), Formula Element #7 (Sort Relative Positions of Rank), N function, IF function and TEXTJOIN function. This is the final formula.
10. (20:30) Summary, Closing Note.

[masterof]

Truly amazing formula. Without watching this video, even looking at it for hours, will not have a clue what does it do!

[Al-Ahdal]

OMG!!! This formula constructions depressed me, too complex, I guess Mike is the only expert who can build such formulas, videos are awesome and excellent as usual.

[jazzista]

Bravo to Bill and Mike... my goodness My head is still spinning but great explanation of how to construct this beatutiful array formula.

[BillSzysz]

I am very impressed by your perfect clarifications!!!
Your skills are unreachable to me
Thanks Mike :-)))

[mirceadragota]

really nice approach! only this kind of tuts can teach what a good workflow can do

[niteeshmishra]

My dear Teacher Love you From India ..

[mikeburton]

Like putting a puzzle together...WOW!!

[paulFm]

Mike you're the best!

Your videos allowed me to obtain my MOS Excel Certification! Many of you classes have also helped me to make some amazing Dashboards that my brother uses for his job. If you're interested, I'd love to share them with you.

If you're interested, let me know

[aprildu]

I never could come up with such formulas myself! Impressive! I normally would  just take the short  cut of macros:)

[jordansee]

Can you do this still if the Data Set is in a row or does it have to be in a column?

[MichaelVB]

Hi excited to spend some time mastering these formulas.  Thank you so much for a wonderful presentation.    Also excited to see "Printing will this  be available?

[Al-Ahdal]

I guess with UNIQUE, SORT and FILTER in Office 365, the formula construction becomes easy. My head spin when I see this formula construction, that really depressed me. Please take the same video for UNIQUE, SORT & FILTER, and kindly solve this problem. Mike you are too good, not good but EXCELLENT (Excel Heavy Weight).

[elizabethannjensen]

Hey. So this might be a silly question. I'm getting stuck on the unique count. It's returning a 1 not 9. I'm on a Mac. Do you know what is causing this?

[RAHULfromIND]

Hi Mike, How come you are getting unique count as 9, I am getting it as 10

[acastillo]

Where can I find tutorials on adding info(first name) on one excel sheet and it generates it on sheet #2?

[dannyzhu]

Amazing. When the printing 3 is going to be out in amazon?

[kjvstats]

Mike, one word for you...Transmogrified.

[levelzeroD]

You uploaded a video the same time as another youtuber. (FOR REAL)

[drsteele]

MS has created several new functions in its new calc engine for Excel365, and this is the new solution, which you all will agree is slightly simpler:
=TEXTJOIN(", ", , SORT(UNIQUE(FILTER(A2:A15, A2:A15<>""))))

[markeaton]

Hi, not sure why but I'm getting 10 for the unique count in D8 following your exact method. List = Mike, Piotr, 123, 1, Rad, Bill, Steven, 53, Alexandra and Bellen. Total of 10 unique values. Help please? :(