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.
Awesome!!! I wrote a whole book about array formulas with chapters 15 to 19 all about extracting data, getting unique lists and sorting with formulas. Did you not buy the book from Amazon? It is called Ctrl + Shift + Enter, Mastering Excel Array Formulas. If you cannot buy it, send me your address and I will send you one for free.
Thanks a zillion, sir. It's like a blessing to me. I sent you a text today through your youtube channel's message option. I don't know whether you got it. Thanks again for everything, sir.
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?
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?
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)
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.
Unique list and sorting formulas are among the most difficult in Excel - chapters 15 - 19 in my Ctrl + Shift + Enter book show some of the huge formulas for such tasks. Although I am sure that there is a way to consider case, I do not recall see such a formula and I have not created such a formula. The problem with formulas like this one to create a unique list from Mixed Data (formula approximately what is in chapter 19): INDEX(ArrayUniqueItems,MATCH(ArrayRelativePositionsCountEqualNoUniqueItems,MMULT(--(ArrayUniqueItems>=TRANSPOSE(ArrayUniqueItems)),ROW()^0),0)) Is that it relies on the >= operator which does not consider case. There are functions like FIND that are case sensitive, But I have not ever created such a formula. Anyone else know of a way?
Thanks for your reply, Mike. I was hoping you could pull a rabbit out of your magic hat on this one. I tried inserting the EXACT function into various locations of your Formula, but alas, it will only give me TRUEs or FALSEs. Maybe Bill Szysz has a solution?
Case sensitive, unsorted version is easy to get with a helper column but version with sorting is much much more complicated. I am not sure if it is possible exactly like in excel. But not exactly like in excel, but very similar, is possible. However in similar case i would choose VBA or PQ (both solution are more simple than formulas - especially PQ)
Thanks for your comments, Bill. I'm a novice at PQ at the moment so I can't visualise what your solution is. Mike, you're pretty slick with PQ...any ideas?
Bill, you must get Excel 2016!!!! Why don't you buy it? Can I buy it for you? It can be a birthday present, Christmas Gift, Summer Solstice Gift all wrapped into one!!! : ) Send me your paypal and I will transfer $
Anybody know how to use this on a table but have the results return data from the visible rows only? I find that when I'm filtering the table data I'm still getting ALL the unique records regardless of the filter.
Beautiful formula, perfect step through diagnosing the formula parts, and example for both lists and tables.
You sir, are thorough. Well done!
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.
Awesome!!!
I wrote a whole book about array formulas with chapters 15 to 19 all about extracting data, getting unique lists and sorting with formulas. Did you not buy the book from Amazon? It is called Ctrl + Shift + Enter, Mastering Excel Array Formulas. If you cannot buy it, send me your address and I will send you one for free.
Words of appreciation from your end are highly overrated to me, Sir. I'll buy it for sure real soon. Thanks again.
If you send me your address, I have some books that have the wrong Table of Contents, but rest of book is fine - so i can send to you for free! : )
I sent the book out today!
Thanks a zillion, sir. It's like a blessing to me. I sent you a text today through your youtube channel's message option. I don't know whether you got it. Thanks again for everything, sir.
I needed the second example for my table...
Outstanding THANK YOU!!!
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?
These are great formulas, particularly the one in second example. Thanks Mike and Bill.
Glad you like them!
Hi mike, is there any formula to lookup a value in single cell contains multiple values
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?
It could be clearer if the key terms were on a different table of the evaluated celll. Thanks for sharing
Another amazing video! It's possible to instead of using textjoin use the substitute function in earlier excel versions? Thank you!
Impressive way to explain complicated concept..
Well done !!!
Glad you like the "make complicated things less complicated" video, Muddassir!!!!
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)
can this be adjusted to update when a filter is applied to the table data?
Carrie, I'm looking for the same thing, did you find a solution for this?
Really cool! Can't wait for our department to get Office 365 so I can try out these new functions!!! ;)
Glad you like it! I hope you get Excel 2016, Office 365 soon!
Thanks, I enjoy all ExcelisFun videos. Very useful
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.
Unique list and sorting formulas are among the most difficult in Excel - chapters 15 - 19 in my Ctrl + Shift + Enter book show some of the huge formulas for such tasks. Although I am sure that there is a way to consider case, I do not recall see such a formula and I have not created such a formula. The problem with formulas like this one to create a unique list from Mixed Data (formula approximately what is in chapter 19):
INDEX(ArrayUniqueItems,MATCH(ArrayRelativePositionsCountEqualNoUniqueItems,MMULT(--(ArrayUniqueItems>=TRANSPOSE(ArrayUniqueItems)),ROW()^0),0))
Is that it relies on the >= operator which does not consider case. There are functions like FIND that are case sensitive, But I have not ever created such a formula.
Anyone else know of a way?
Thanks for your reply, Mike. I was hoping you could pull a rabbit out of your magic hat on this one.
I tried inserting the EXACT function into various locations of your Formula, but alas, it will only give me TRUEs or FALSEs.
Maybe Bill Szysz has a solution?
Case sensitive, unsorted version is easy to get with a helper column but version with sorting is much much more complicated. I am not sure if it is possible exactly like in excel. But not exactly like in excel, but very similar, is possible.
However in similar case i would choose VBA or PQ (both solution are more simple than formulas - especially PQ)
I love it: PQ is the way to go!
Thanks for your comments, Bill. I'm a novice at PQ at the moment so I can't visualise what your solution is. Mike, you're pretty slick with PQ...any ideas?
i do not have textjoin function in office 2016 then how we do that
Such amazing content! Thank you very much!
You are welcome very much!
Great formulas!
: )
Hi Mike, how to transpose that unique column into row column? I have searched it in youtube for 6 hours and i got nothing
Thanks Mike :-))
I can see bright future for TEXTJOIN :-))
You are welcome... Opps... I mean Thanks Bill Szysz!!!!
Thanks for your amazing sorted version at mr excel!!!!
Bill, you must get Excel 2016!!!! Why don't you buy it? Can I buy it for you? It can be a birthday present, Christmas Gift, Summer Solstice Gift all wrapped into one!!! : ) Send me your paypal and I will transfer $
Very slick indeed! Do you have a link handy for the thread on mrexcel that shows a method for producing a sorted version?
www.mrexcel.com/forum/excel-questions/954645-sorted-single-cell-list-alpha-numeric.html#post4585324
Anybody know how to use this on a table but have the results return data from the visible rows only? I find that when I'm filtering the table data I'm still getting ALL the unique records regardless of the filter.
Mike you are amazing. Thanks a lot. and BRAVO!!!
Really useful thank you
You are welcome!
Wao, Amazing, Thank you
You are welcome!
xlnt
Glad you like it!