GROUPBY Function to Group Transactions with No Transaction Number. EMT 1855
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1854-...
Learn how to group transactions with no key or invoice number using dynamic spilled array formulas. Group By when there is no key column and there are duplicates and empty cells. How to group transactions with no transaction number and repeating dates and empty cells.
Topics:
1. (00:00) Introduction
2. (00:27) Teammates!
3. (00:38) Create Unique Identifier with SCAN
4. (03:06) GROUPBY Function
5. (03:28) HSTACK, SUM and ARRAYTOTEXT
6. (04:44) DROP Function to finish report.
7. (05:29) Summary
8. (05:45) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #reporting #groupby #groupbyfunction #PivotBy #pivottable #pivot
I love it when several "tiny" functions can be put together to do the "impossible".
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!
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
That Scan function is amazing ... thanks Mike
Glad you liked it : ) : )
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!!!! : )
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) 🤗
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
Amazing 😍
Glad you like it!!!
Thank you so much amazing Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : )
Meraviglioso
Amazing Thank you kb
You are welcome!!!!!!
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!!!!
Now I’m curious how power query will do this, it’s really cool 😎
Monday we will find out : ) : )
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?
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 :)
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...
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...
: ) : ) : ) : )
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!
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
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))