Unique List of Case Sensitive Items? Expandable Range in Spilled Array Formula? EMT 1792

[@mattschoular8844]

So after pondering for a minute, I think I will go with the PQ option. Thanks Mike...

[@excelisfun]

It took you that long? lol : ) : ) : ) : ) You are welcome, Matt!!!

[@michaelbrown8821]

I agree with the rest that PQ is the easier option, but just knowing that there is a formula to do this is indeed..epic. Nicely done, Mike.

[@excelisfun]

PQ wins : ) Glad you like the fun, Michael!!!!

[@HusseinKorish]

OMG .... i didn't know that INDEX function can do that .....Thanks Mike

[@excelisfun]

O, Yes!!!!! INDEX has been able to create dynamic ranges since it first came out many decades ago : ) : ) : ) : )

[@ivanmamchych5802]

Thank you, Mike, for interesting lesson. If Power Query option is clear and obvious, the second scenario requires "mental acceptance and brain digestion" 🙂. I could hardly believe it can be done, but after all - I am taking my hat off, sir... Awesome!

[@excelisfun]

I am so glad that you enjoy the videos. Thanks for the hats off, Ivan!!!!!

[@darrylmorgan]

Boom!2 Awesome Solutions,Power Query A Lot Easier If You Don't Mind Refreshing...Thank You Mike :)

[@excelisfun]

Yes, indeed, PQ Simplicity is well worth the price of a refresh : )

[@lmk001]

Amazing. I don't understand a lot of it but if I ever needed the formula to do it outside of power query I have it.

[@excelisfun]

Glad you like it : ) But the PQ easy way is the way to go!!

[@SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Fellow Teacher : ) : ) : )

[@chrism9037]

1st! Excellent Mike! I like the simpler solution of PQ

[@excelisfun]

First Place Tie: Syed Hassan and Chris M!!!! That is OK, I got 1st trophies for both of you!!! : ) : ) : ) Nooooo doubt, PQ is so simple. Thankfully, Microsoft created M Code in Power Query to be case sensitive : )

[@ricos1497]

Nice video Mike. My first thought was to use CHAR() and CODE() to get the UNIQUE list. Thus: =BYROW(IFERROR(CHAR(UNIQUE(CODE(MID(fProd[Product],SEQUENCE(,MAX(LEN(fProd[Product]))),1)))),""),LAMBDA(a,TEXTJOIN("",TRUE,a))) will return the unique product list, which you can then do the count, headers etc on. You can also use EXACT too, but rather than compare to the table, just total the upper or lower case count for each product name, which would always be different if the names are the same but with different case: =LET(p,fProd[Product], m,MAX(LEN(p)), t,MID(p,SEQUENCE(,m),1), r,BYROW(--EXACT(t,UPPER(t)),LAMBDA(a,SUM(a))), u,CHOOSECOLS(UNIQUE(HSTACK(p,r)),1), h,HSTACK(u,COUNTIF(p,u)), h) I'm guessing that the above would be marginally quicker on a large table due to not having to reference the above rows in each row. Probably!

[@excelisfun]

Awesome, Rico S!!! Very Clever : ) I have added the formulas to the download workbook so the Team can have even more fun : )

[@johnborg5419]

Thanks Mike. The Formula is Fantastic!!!! :) :)

[@excelisfun]

You are welcome, Formula Guy John!!!!

[@Softwaretrain]

Thanks for the great solution. Power query shine in this example but for the alternative formula my solution is: =LET( data,fProd[Product], product,REDUCE(,data,LAMBDA(a,r,IF(SUM(--EXACT(a,r)),a,VSTACK(a,r)))), count,BYROW(product,LAMBDA(r,SUM(EXACT(r,data)))), tbl,HSTACK(product,count), result,VSTACK({"Product","Count"},tbl),result)

[@mohamedchakroun4973]

Great mike :-) we can't believe that excel could do that. Always i like excel formulas than pq because it make us learn and discover many things:-)

[@excelisfun]

Formulas are fun!!!! Glad you like it, Mohamed : )

[@BhavyaGupta]

Great solution Mike sir, especially the part where you used Dynamic reference with Index 😀 This is what I tried with some of the new functions - =LET(rng, Table1[Column1], rng_1, SCAN("",SEQUENCE(ROWS(rng)), LAMBDA(x,y,LET(a,TAKE(rng,y),b, INDEX(rng,y,1),IF(SUM(--EXACT(b,a))>1,"",b)))), rng_2, FILTER(rng_1, rng_1""), rng_3, MAP(rng_2, LAMBDA(z, SUM(--EXACT(z,rng)))), HSTACK(rng_2,rng_3))

[@excelisfun]

Great Spilled Formula, Bhavya!!! I have added your solution to the download Excel file so that the rest of the Team can see it also : ) Go Team!!!!

[@BhavyaGupta]

@@excelisfun Glad it found place there😃

[@excelisfun]

@@BhavyaGupta Me too : ) : ) : )

[@nadermounir8228]

Thanks Mike for this great video. The thing that confuses is what the value of r is here ? The count is a bit difficult to understand how it was done ?

[@excelisfun]

r represents each row in the array inside of BYROW. It is difficult to understand at firts becasue it is a whole new way of building worksheet formulas. 100% conceptually different than how we did it for last 40 years.

[@nadermounir8228]

@@excelisfun I got it now. Thank u so much Mike.

[@excelisfun]

@@nadermounir8228 You are welcome so much!

[@KevinPGA]

Continually AMAZING!!! 👍👍👍

[@excelisfun]

Glad it continually amazes, Kevin!!! I like the new Fred Finstone icon : )

[@B1897forzajuve]

Hi Mike. I was missing these videos. Not saying that the statistical analysis playlist is not amazing, but I don't have a lot of time to start the playlist from the very beginning. Hopefully this summer I will finish it. Do you have any update on when the new functions will be available in Excel?

[@excelisfun]

No worries, I cover such a broad range of topics in Excel and Power BI that you can always pick and choose just what you want : )

[@excelisfun]

I do not have an update. But for Microsoft 365, it really should be soon. Functions like VSTACK, HSTACK, BYROWS and so many more just change everything and makes it more fun.

[@Excelambda]

Great video!! ✌😉 The main idea is to create expandable ranges. The INDEX(...):r is super ok for spreadsheet ranges, but can not work with arrays. As a simple experiment if you change =LET( p,fProdAn[Product]&"", ...(appending an empty string, is an array calculation) the formula will return an error. So only if we want larger spectrum for our formulas or to contain them with other formula constructions, or lambdas, they have to accept array calculations. =LET(a,fProdAn[Product], m,MAP(SEQUENCE(ROWS(a)),a,LAMBDA(x,y,SUM(--EXACT(INDEX(a,SEQUENCE(x)),y)))), f,FILTER(a,m=1),b,BYROW(f,LAMBDA(x,SUM(--(EXACT(x,a))))), VSTACK({"Product","Count"},HSTACK(f,b)))

[@excelisfun]

Are you saying the above posted formula will not have this problem? You and I worked on this one a few months back, and both of our solutions did not work when there was a tie, like Carlota and CARLOTA. So this is why I switched to INDEX:r So you are prosing INDEX(a,SEQUENCE(x)) rather than INDEX:r, right? I will go and try it now. Thanks for your wise guidance, O Spilled Formula Master ExcelLambda : ) : ) : ) : )

[@excelisfun]

I see - it works great. I have added it to download for the Team : ) : ) : ) : )

[@Excelambda]

@@excelisfun INDEX(...):r works fine with ranges, it is short and elegant, is useful as last stage dashboard reports, INDEX(a,SEQUENCE(x)) creates expandable range anywhere. This is the only difference. ✌🙏 Anyway, for fun only, I took the challenge to write a formula with no expandable ranges and without EXACT ☺ =LET(a,fProdAn[Product], m,IFERROR(CODE(MID(a,SEQUENCE(,MAX(LEN(a))),1)),32), u,UNIQUE(m),p,TRIM(BYROW(u,LAMBDA(x,TEXTJOIN(,,CHAR(x))))), b,BYROW(u,LAMBDA(x,SUM(--BYROW(m=x,LAMBDA(y,AND(y)))))), VSTACK({"Product","Count"},HSTACK(p,b))) 😉

[@yasertaherkhani3174]

Very Nice❤

[@excelisfun]

Glad you like it, Yaser!!!

[@markpodesta4605]

Thank you Mike. Magic! 😀

[@excelisfun]

You are welcome, Mark!!!!

[@MalinaC]

Thanks for awesome video! 🙂👍

[@excelisfun]

You are welcome, Teammate Malina C : ) : ) : )

[@BaniMoniah]

We’ll take the power query option 😅

[@excelisfun]

So far most of the votes are for PQ : )

[@hermitcard4494]

Question: How much Office App(Android)'s Excel can be pushed to do this and other magic stuff?

[@excelisfun]

You mean Excel on the phone? If you are asking about Phone Excel, that app does almost nothing as compared to a Windows PC Microsoft 365 Excel. Phone Excel is not a real analytical tool. It can do some quick and simple stuff, though : )

[@hermitcard4494]

@@excelisfun Yeah, I guess it doesn't have the same capability as PC Excel, but the question is how much can mobile Excel be pushed? What are its limits?

[@excelisfun]

@@hermitcard4494 Microsoft's plan is to have Excel the same on all devices. But they are not close to achieving this. I do not think that currently the phone version can be pushed too far. Here is a KZbin channel that shows phone Excel (lots of finance): kzbin.info/door/Umr9ElmdRotCIpcvA-3QRQ

[@excel_along_the_way]

Hi Mike, never used PQ, I think I will start now.😂

[@excelisfun]

Really? You never used Power Query? It can do so many wonderful that that the other tools can not do as well!!! Today is a new begging for you: Power Query happiness : )

[@excel_along_the_way]

@@excelisfun , I started this week with PQ and managed to clean a messy data list out of Oracle. It is very intuitive but there are things that work differently to excel like the TRIM feature. When I got my head around that I managed to solve the query. Keep well.

[@murat.hasanoglu]

Epic

[@excelisfun]

Glad it is epic for you, Murat!!!!

[@ExcelWizard]

Great video Another function with Case sensitive FILTERXML =LET(z,fProd[Product],p,FILTERXML(""&TEXTJOIN("",,z)&"","//m[not(preceding::*=.)]"),IF({1,0},p,MMULT(--EXACT(p,TRANSPOSE(z)),ROW(z)^0)))

[@excelisfun]

FILTERXML beautiful, Excel Wizard!!!!! I have added it to download file to all Team can see : ) Go Team!!!!!!!!!

[@ogwalfrancis]

For Dynamic Array method, very many times of watching this video is required to understand it😂😂😂, I go with Power Query but what if you don't want a case sensitive count but you want to use power query where there are same items of different cases

[@excelisfun]

Very true. PQ easy, Spilled Array Formula hard ; )

[@Al-Ahdal]

1st comment

[@excelisfun]

First Place Tie: Syed Hassan and Chris M!!!! That is OK, I got 1st trophies for both of you!!! : ) : ) : )

[@Excelambda]

For fun , no expandable range : =LET(p,fProdAn[Product], t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a), b,BYROW(u,LAMBDA(x,UNIQUE(FILTER(t,x),1))), s,BYROW(u,LAMBDA(x,SUM(x))), VSTACK({"Product","Count"},HSTACK(b,s)))

[@Excelambda]

Or this one, still no expandable , embeds a lambda b(i), for b() calculates product, for b(1) counts: =LET(p,fProdAn[Product], t,TOROW(p),a,EXACT(p,t),u,--UNIQUE(a), b,LAMBDA([i],BYROW(u,LAMBDA(x,LET(y,XLOOKUP(1,x,t),z,SUM(x),IF(i,z,y))))), VSTACK({"Product","Count"},HSTACK(b(),b(1))))

[@nileshshah7728]

=TEXTAFTER(UNIQUE(BYROW(EXACT(fProdAn[Products],TRANSPOSE(fProdAn[Products]))*1,LAMBDA(a,TEXTJOIN("-",TRUE,a)))&"|"&fProdAn[Products]),"|") This will give UNIQUE case sensitive list of Products