Wow, super awesome Mike! That formula was crazy. My default here would be PQ (I know that's your next video), and then I could skip the Excedrin trying to come up with your formula lol
Thanks for the video. Very cool use of some of the newest functions. Great to have a standby when the Power Query (which would be my goto) would be overkill. Can't wait for the next video in the series.
Good way to clean up a hot mess.Pivot Tables are one of my distant menories--like Advanced Filter and my Senior Prom--since I discovered GROUP/PIVOTBY!. Great video!!
Dear Mike, I doubt anyone will constantly receive unstructured data like this, but if it happens you can use the following LAMBDA: 😃 =LAMBDA(Structuring, LET(Date,OFFSET(INDEX(Structuring,,1),-1,), TabComplete,OFFSET(Structuring,-1,), Array,DROP(GROUPBY(SCAN(0,Date"",SUM),TabComplete,HSTACK(SUM,ARRAYTOTEXT,SUM),3,0),1,1), Result,VSTACK(Array,HSTACK("Total Geral","",SUM(CHOOSECOLS(Array,3)))), Result))(fPaintStoreF) 🤗
Nothing too important, but I want to mention that we could even remove the ("") part from the formula as follows: =DROP( GROUPBY( SCAN(0,fPaintStoreF[[#All],[Date]],SUM), fPaintStoreF[#All], HSTACK(SUM,ARRAYTOTEXT,SUM), 3,0), 1,1)
Sir you...make me master in excel and PBI with PQ 😊 but sir i want know when did this Groupby nd PivotBy fx will be available in our MS365 bcs we aren't using beta version...
Great video as always. Just a thought - I think if you said "no headers' in your "GROUPBY" 4th argument as 0, you wouldnt then have to DROP the first row. You'd still have to drop the first column here so not much gained, but hey!
Yes, but you’d included the field names in the data range, so they were there anyway - what the include header does is give you the row with SUM, ARRAYTOTEXT and SUM I think. I dont have the GROUPBY function yet, but you could try it to see if I am right and you use 0 instead of 3 in that argument.
Actually, I was wrong. =DROP(GROUPBY(SCAN(0,fPaintStoreF[[#All],[Date]]"",SUM),fPaintStoreF[#All],HSTACK(SUM,ARRAYTOTEXT,SUM),0,0),0,1) still generates the titles SUM, ARRAYTOTEXT and SUM; I didn't expect it to do that. Thanks as always, really useful to know what actually works :)
GROUPBY is fantastic and this my try, =LET(j,SCAN(0,fPaintStoreF[Date],LAMBDA(a,d,a+(d>0))), DROP(REDUCE(0,UNIQUE(j),LAMBDA(a,v,LET(i,FILTER(fPaintStoreF,j=v), VSTACK(a,HSTACK(v,@i,ARRAYTOTEXT(INDEX(i,,2)),SUM(DROP(i,,2))))))),1))
How to make a sequence number with criteria in dynamic array Like 1 product a 2 product a 3 product a 1 product b 2 product b 1 product c Like this Thank you sir
Impressive! Your explanation has made this formula incredibly easy to understand.
👍👍👍
Thanks for your help, Teammate. Wait... I thought you already created a formula almost exactly the same!
I love it when several "tiny" functions can be put together to do the "impossible".
Thanks Mike!!! HStack(
Sum, Arraytotext,Sum) that was Mind blowing!!
Blew my mind too : ) : )
Wow, super awesome Mike! That formula was crazy. My default here would be PQ (I know that's your next video), and then I could skip the Excedrin trying to come up with your formula lol
skip the Excedrin lol ... I guess it is sorta true lol*100
Thanks for the video. Very cool use of some of the newest functions. Great to have a standby when the Power Query (which would be my goto) would be overkill.
Can't wait for the next video in the series.
Me either!!!! : )
Good way to clean up a hot mess.Pivot Tables are one of my distant menories--like Advanced Filter and my Senior Prom--since I discovered GROUP/PIVOTBY!. Great video!!
Senior Prom lol
That Scan function is amazing ... thanks Mike
Glad you liked it : ) : )
Dear Mike,
I doubt anyone will constantly receive unstructured data like this, but if it happens you can use the following LAMBDA: 😃
=LAMBDA(Structuring,
LET(Date,OFFSET(INDEX(Structuring,,1),-1,),
TabComplete,OFFSET(Structuring,-1,),
Array,DROP(GROUPBY(SCAN(0,Date"",SUM),TabComplete,HSTACK(SUM,ARRAYTOTEXT,SUM),3,0),1,1),
Result,VSTACK(Array,HSTACK("Total Geral","",SUM(CHOOSECOLS(Array,3)))),
Result))(fPaintStoreF) 🤗
Thank you so much amazing Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : )
Amazing 😍
Glad you like it!!!
Nothing too important, but I want to mention that we could even remove the ("") part from the formula as follows:
=DROP(
GROUPBY(
SCAN(0,fPaintStoreF[[#All],[Date]],SUM),
fPaintStoreF[#All],
HSTACK(SUM,ARRAYTOTEXT,SUM),
3,0),
1,1)
Dang!!!! Totally Rad without the "" : ) I just added your formula to the download file. Go Team!!!!
Awesome!!! I just put it in download file : ) Go Team!!!!
Amazing Thank you kb
You are welcome!!!!!!
Meraviglioso
When I open a new excel worksheet with some existing ones, the last edited worksheet from existing ones pops-up in the taskbar. How can we stop this?
I am not sure how to fix that. Anyone else know?
Now I’m curious how power query will do this, it’s really cool 😎
Monday we will find out : ) : )
Just curious how many do not have the groupby and pivotby functions at this time. Thanks, in advance for your response.
IMO, GroupBy and PivotBy are in the Beta channel only.
@@GeertDelmulle Thanks!
Sir you...make me master in excel and PBI with PQ 😊 but sir i want know when did this Groupby nd PivotBy fx will be available in our MS365 bcs we aren't using beta version...
Great video as always. Just a thought - I think if you said "no headers' in your "GROUPBY" 4th argument as 0, you wouldnt then have to DROP the first row. You'd still have to drop the first column here so not much gained, but hey!
I wanted the field names in the data set so GROUPBY would use them as labels.
Yes, but you’d included the field names in the data range, so they were there anyway - what the include header does is give you the row with SUM, ARRAYTOTEXT and SUM I think. I dont have the GROUPBY function yet, but you could try it to see if I am right and you use 0 instead of 3 in that argument.
@@TheMrMishutka, Cool! Did you try it? I want to see your formula : )
@@excelisfun so you had DROP(GROUPBY(SCAN(0,fPaintStoreF[[#All,[Date]],SUM),fPaintStoreF[#All],HSTACK(SUM
Actually, I was wrong. =DROP(GROUPBY(SCAN(0,fPaintStoreF[[#All],[Date]]"",SUM),fPaintStoreF[#All],HSTACK(SUM,ARRAYTOTEXT,SUM),0,0),0,1) still generates the titles SUM, ARRAYTOTEXT and SUM; I didn't expect it to do that. Thanks as always, really useful to know what actually works :)
Office 365 Current channel here this feature hasn't been released yet same thing for the python functionalities
Yes, Microsoft has not said when even through they said soon...
GROUPBY is fantastic
and this my try,
=LET(j,SCAN(0,fPaintStoreF[Date],LAMBDA(a,d,a+(d>0))),
DROP(REDUCE(0,UNIQUE(j),LAMBDA(a,v,LET(i,FILTER(fPaintStoreF,j=v),
VSTACK(a,HSTACK(v,@i,ARRAYTOTEXT(INDEX(i,,2)),SUM(DROP(i,,2))))))),1))
How to make a sequence number with criteria in dynamic array
Like
1 product a
2 product a
3 product a
1 product b
2 product b
1 product c
Like this
Thank you sir
: ) : ) : ) : )