Excel Magic Trick 1309: Extract Unique List and List in Single Cell: TEXTJOIN Array Formula

Download Excel Files:

Learn how to create an array formula to display a unique list in a single cell. This formula comes from Bill Szysz at YouTube. See the functions: MATCH, ROW, IF and the new Excel 2016, Office 365 function TEXTJOIN. Also see formula that is used on an expandable Excel Table and see Excel Table Formula Nomenclature (Structured References).

[MrYdoineedthis]

Beautiful formula, perfect step through diagnosing the formula parts, and example for both lists and tables.
You sir, are thorough. Well done!

[jimmatthews]

I needed the second example for my table...

Outstanding THANK YOU!!!

[sim]

Sir, You just made my dream true. For the last two years, I have been trying to find out how to extract unique values from a list whether it is a text or a number. You just gave me the clue along with a new calculation. I watched your calculation to extract multiple values looking up one using Aggregate+Rows nested in Index function. And fusing the two (This one and that one), I did the extracting calculation using the range you"ve shown us in this video.

By the way, This is what I found out={Index(If(Match(A5:A12, A5:A12, 0)=(Row(A5:A12)-Row(A5)+1), A5:A12), ""), Rows(D$5:D5))}.
Thanks a zillion.

[deninsrmic]

These are great formulas, particularly the one in second example. Thanks Mike and Bill.

[muddassirkaleem]

Impressive way to explain complicated concept..
Well done !!!

[ricardocalleja]

It could be clearer if the key terms were on a different table of the evaluated celll. Thanks for sharing

[rajeshjha]

Hi mike, is there any formula to lookup a value in single cell contains multiple values

[jloojl]

Thanks, I enjoy all ExcelisFun videos. Very useful

[mrjparlin]

Really cool! Can't wait for our department to get Office 365 so I can try out these new functions!!! ;)

[JeremyMcMahan]

Is there a way to convert a delimited list stored in a single cell (or returned by the TEXTJOIN function) back into a proper array so it can be used in array functions?

[tomoleusz]

Such amazing content! Thank you very much!

[lanhthientu]

Thank for ur skill, Sir. It so helpful. Can you help me more, Sir. With your table, We need show same data with 1 condition. there is the value of Sales column is repeat?

[josebetancourt]

Another amazing video! It's possible to instead of using textjoin use the substitute function in earlier excel versions? Thank you!

[BillSzysz]

Thanks Mike :-))
I can see bright future for TEXTJOIN :-))

[ArkWorksCNC]

Excellent Video. I need some guidance taking it one step further though. I have a large data set that has this element of data, a list of sometimes duplicated or blank values, that I need to condense to a single row of joined-text. There's an extra layer though in my challenge where each set of these varying values is a subset of another repeating, unique number. For instance, N.123456 repeats 6 times. Within those 6 rows I have another column that has sometimes duplicated or blank values that is specific to that one record, N.123456. I need to join all text that is unique to just that one record (N.123456) and then move to the next line dynamically to evaluate and produce results for the same scenarios but now for a different record ex: N.123457. I was able to reproduce your solution for a single record (N.123456 x6) using the OFFSET function that dynamically adjusts the arrays to the new set of records, but I can't get this solution that you've offered to follow-suit. Any recommendations would be GREATLY appreciated. I'm really stumped here. N.123456 | BILL                          UNIQUE RESULT NEEDED = N.123456 | Bill, Sue, JakeN.123456 | SUE                           UNIQUE RESULT NEEDED = N.123457 | Phillip, Joshua, Nathan, MichaelN.123456 | (Blank)N.123456 | BILLN.123456 | JAKEN.123456 | (Blanks)N.123457 | Phillip                       N.123457 | (blank)N.123457 | JoshuaN.123457 | NathanN.123457 | MichaelN.123457 | (blank)

[shau]

Mike you are amazing. Thanks a lot. and BRAVO!!!

[carriecaldarera]

can this be adjusted to update when a filter is applied to the table data?

[rajkirandhyani]

i do not have textjoin function in office 2016 then how we do that

[hota]

Hi Mike, how to transpose that unique column into row column? I have searched it in youtube for 6 hours and i got nothing

[kjvstats]

Mike, Great video - I loved the nested formula.
Question: Can you amend the formula to create the array by listing unique text that is Case Sensitive?
Observation: the current formula will not distinguish words such as, "the" and "The" as being unique.