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.
Could you please guide, how can I able to become good in the construction of complex formulas and requirements? This one is the HARDEST.... and its construction is too complex.
Mike! is there any possibility to use the unique list obtained in D11 as a source for a drop down list in a cell without creating a helper column which we do in normal course?
If you don't need it sorted and you want a formula, try this video: ruclips.net/video/3u8VHTvSNE4/видео.html better to do it with Power Query, here is a video for that: ruclips.net/video/3ICk356kEZo/видео.html If you want sorted unique list with formula, not in single cell but rather in multiple cells, try chapter 19 in the book.
Sir You are God of Excel Learning Student Sir, I have a question from you "" When I take the data from the first sheet to the second sheet, VLOOKUP that my data is not applicable due to plausibility. I already watch previous VLOOKUP 175 video playlist , VLOOKUP week video ,and other VLOOKUP videos in our Excelisfun but my problem not resolved Please make a video. ###Sorry for the word error because english is not very powerful my
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
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?
Hi Mike.....very excited to spend some time mastering these formulas. Thank you so much for a wonderful presentation. Also excited to see "Printing 3".....when will this be available?
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).
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""))))
Here is the link to the MrExcel forum that caused all this mind-warping Excel action. Awesome stuff, Mike and Bill!!! www.mrexcel.com/forum/excel-questions/954645-sorted-single-cell-list-alpha-numeric.html
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? :(
Precisely the same as yours using the exercise #1313 template, : =COUNT(IF(Data"",IF(MATCH(Data,Data,0)=ROW(Data)-ROW(A8)+1,ROW(Data)-ROW(A8)+1)))and just for reference here is the array formula used in D7 and ensuring to ctrl-shift=enter for both: =IF(Data"",IF(MATCH(Data,Data,0)=ROW(Data)-ROW(A8)+1,ROW(Data)-ROW(A8)+1))What's interesting is I copied your formula to a tee, as in I worked along with the video step by step. I've tried it three times and I still get 10 not 9 as the result. The other interesting thing is the Unique Count box already populated in the 1313 workbook is it is showing 10 also. Appreciate your help.
Wow! That's sneaky, the error is hiding right in front of me! Thank you Mike! I was focused on using this formula for my particular problem so much that I completely missed that! Thank you again, take care...(Crawls away to hide)
Truly amazing formula. Without watching this video, even looking at it for hours, will not have a clue what does it do!
Glad the video helps to give a clue!
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.
Yes, sorting formulas are just about THE hardest in Excel ... : (
Could you please guide, how can I able to become good in the construction of complex formulas and requirements? This one is the HARDEST.... and its construction is too complex.
Bravo to Bill and Mike... my goodness My head is still spinning but great explanation of how to construct this beatutiful array formula.
Glad you like it! My head spins too, but if we slow down and go one piece at a time, we can do it! Thanks to Bill Szysz for the great formula!
really nice approach! only this kind of tuts can teach what a good workflow can do
Glad the workflow was helpful!!!
Can you do this still if the Data Set is in a row or does it have to be in a column?
I am very impressed by your perfect clarifications!!!
Your skills are unreachable to me
Thanks Mike :-)))
And your skills are unreachable to me!!! : ) That is why we are on an awesome team: Online Excel Team!!! Go Team!
Two great men appreciating each other. A nice team work indeed. :)
Mike! is there any possibility to use the unique list obtained in D11 as a source for a drop down list in a cell without creating a helper column which we do in normal course?
Go Team!! : )
If you don't need it sorted and you want a formula, try this video:
ruclips.net/video/3u8VHTvSNE4/видео.html
better to do it with Power Query, here is a video for that:
ruclips.net/video/3ICk356kEZo/видео.html
If you want sorted unique list with formula, not in single cell but rather in multiple cells, try chapter 19 in the book.
My dear Teacher Love you From India ..
Glad to help, Niteesh!!!!
Sir You are God of Excel Learning Student Sir, I have a question from you "" When I take the data from the first sheet to the second sheet, VLOOKUP that my data is not applicable due to plausibility. I already watch previous VLOOKUP 175 video playlist , VLOOKUP week video ,and other VLOOKUP videos in our Excelisfun but my problem not resolved Please make a video. ###Sorry for the word error because english is not very powerful my
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
Thank you for your kind words. You can send it to me at excelsifun at gmail, but I am very backed up with work right now : (
You're welcome. You have positively changed my life as well as many others.I will send the dashboards and hope you find them fun!
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?
crazy XL gymnastics :)
Love this description: XL gymnastics!!! Cuz that's what it is!
I'll be studying this formula for a while
haha... XL size formula :-)
I never could come up with such formulas myself! Impressive! I normally would just take the short cut of macros:)
Macros are good : )
Hi Mike , How come you are getting unique count as 9 , I am getting it as 10
Like putting a puzzle together...WOW!!
It is truth that it is like putting a puzzle together! I love the analogy!
I almost said like a 3D puzzle. I will say though, you make it look easy. Thanks, I have learned lots from your videos and Mrexcel as well!!
I am glad that the videos help and that our Online Excel Team (Mr Excel and all the rest of us) can have fun and help!!
Hi Mike.....very excited to spend some time mastering these formulas. Thank you so much for a wonderful presentation. Also excited to see "Printing 3".....when will this be available?
I am not sure what printing 3 is.
Where can I find tutorials on adding info(first name) on one excel sheet and it generates it on sheet #2?
Amazing. When the printing 3 is going to be out in amazon?
I am not sure. Bill Mr Excel Jelen, the publisher, sent it to press, but it has not shipped yet! I'll probably make a video when it gets out...
Awesome Awesome Awesome !!!
Glad you like it, Glad you like it, Glad you like it!!!
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).
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""))))
invalid formula
Crazy. Just crazy.
Crazy good!?!?
Crazy good techniques.
But also crazy long. To make it better readable I would use names for the parts that repeat.
Great idea!I agree! In my book, I did use names.
Here is the link to the MrExcel forum that caused all this mind-warping Excel action. Awesome stuff, Mike and Bill!!! www.mrexcel.com/forum/excel-questions/954645-sorted-single-cell-list-alpha-numeric.html
Awesome! The link is also in the downloadable workbook!
You uploaded a video the same time as another youtuber. (FOR REAL)
Cool!
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? :(
What is your formula?
Precisely the same as yours using the exercise #1313 template, : =COUNT(IF(Data"",IF(MATCH(Data,Data,0)=ROW(Data)-ROW(A8)+1,ROW(Data)-ROW(A8)+1)))and just for reference here is the array formula used in D7 and ensuring to ctrl-shift=enter for both: =IF(Data"",IF(MATCH(Data,Data,0)=ROW(Data)-ROW(A8)+1,ROW(Data)-ROW(A8)+1))What's interesting is I copied your formula to a tee, as in I worked along with the video step by step. I've tried it three times and I still get 10 not 9 as the result. The other interesting thing is the Unique Count box already populated in the 1313 workbook is it is showing 10 also. Appreciate your help.
Did you check the data? Is it the same?
Wow! That's sneaky, the error is hiding right in front of me! Thank you Mike! I was focused on using this formula for my particular problem so much that I completely missed that! Thank you again, take care...(Crawls away to hide)
Hi again Mike. Hypothetically, what's an alternative to TEXTJOIN if say you don't have Excel 2016? :D
Mike, one word for you...Transmogrified.
I love Calvin & Hobbes! That is exactly what Calvin's Box did: Transmogrified !!! Excel is great at that!
anyone have a udf that can do this?
I do not. Sorry. I bet someone has made one though... Maybe try posting to: mrexcel.com/forum