Worksheet Formulas to Group Transactions with No Transaction Number. Magic Trick 1854
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1854-...
Learn how to group transactions with no key or invoice number using worksheet 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:31) Key Column
3. (01:35) SEQUENCE function 1 to 5
4. (01:43) FILTER function to get dates
5. (01:59) FILTER and TEXTJOIN functions to get descriptions
6. (02:40) SUMIFS to add amounts based on key column
7. (03:03) Summary
8. (03:11) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp, #datatransformation
![NoCap - Yacht Party [Official Music Video]](http://i.ytimg.com/vi/rm_lvDln8kE/mqdefault.jpg)
Excel Magic Trick 1854, 1855 & 1856 all solve the same problem but with different tools: worksheet, dynamic spilled array and then Power Query, respectively. Released dates: 1854 = 2/26/2024, 1855 = 2/29/2024, 1856 = 3/4/2024. The EMT numbers at beginning of video are not correct. I mistakenly listed the numbers as 1852, 1853 & 1854. They should be: 1854, 1855 & 1856 : )
I believe these videos would serve as assets for generations to come. Thanks Mike
agree!
We can not thank you enough for all what you are doing for the world to be Experts in excel , the others make courses and earn hundred thousands or even millions of dollars in Their scientific specializations ، but you make thousands of vedios for free , you are more than legend Mr girvin , I won't exaggerate if I said you are one of the most legend people in human kind history ❤❤😍😍
Thank you very much for your kind words : ) : ) My philosophy that I live by is this: "Make the world a better place and treat everyone with love and caring, fulfill all my duties with happiness, and get rad and have fun doing it!!" So I love what I do here at RUclips : ) : ) : ) : )
Mike is the most talented Excel guru you will ever encounter. An amazing teacher, with a unique talent for teaching, even his delivery is outstanding. There is more, Mike is a very humble person as well. Excel is Fun channel, is a public asset for the Excel community. GOD bless you and your family Mike, thank you for all your outstanding videos.
Thanks for the kind words. I am happy to create public assets : ) : ) : )
Best you tube channel on Excel ever found on YT so far ...Keep going🎉 India
Thanks for your kind words : ) So glad to help!!!
Thanks Mike. Although this was a short video, there are definitely some useful tips here. Keep up the great content, always appreciated.
You are welcome, thanks for the appreciation, Matt : ) : )
Perfect and clever solution. Looking forward to the dynamic spilled array solution. Thank you Mike :)
Glad you like it, Nader!!!! Can't wait for next two videos : )
Thanks Mike, always a treat on a Monday to see a video from you!
I am glad to give out treats each Monday for our Awesome Team!!!!
Thank you! Sequence is awesome! 👏👍
You are welcome, Teammate!!!!
That's Amazing Mike...i'm speechless...se can use this trick in so many situations....thank a lot for inspiring us every time
You are right: so many situations : )
Your work is meaningful. I highly respect on your generosity
Another great video Mike
Glad you like it : ) : ) : ) Wait till the next two videos....
Amazing !!!! Mike
Glad it is amazing for you!!!!
Thanks, this is next level.
Wait till you see the next two videos : ) : ) : ) It is such a common problem, that it deserves next level!
this trick is so cool!
Glad you like it!!!
That's very cool trick Mike .
EXCELLENT SIR..
Glad you like it : ) : )
Thanks Mike. Amazing!!! :) :)
You are welcome, Formula Guy John!!!!
Proves videos don't have to be long to be good!! Great solution to a ubiquitous but completely avoidable probem Like a huge number of real-world spreadsheets it was doomed by its design! LOL
Yep!!!! This happens all the time... Bad Design... Causes fun for us : ) : ) : )
Sir.. fantastic question and solution... Love to note it down🙏
Glad you like it!!!! Two more fun solution to come in next two videos : )
Thanks for the interesting video. Too many datasets passed for ananlysis are missing any form of transaction code, so it is always useful to have grouping method at your finger tips.
So true!!!!
Hi Mike, Thanks for sharing. Your channel has always been my first selection to watch.
I saw your amazing formula in the download Excel file but I want to add one more as below:
=VSTACK({"Date","Description","Total Sales"},DROP(GROUPBY(SCAN(0,fPaintStoreF[Date],LAMBDA(a,c,IF(c="",a,a+1))),fPaintStoreF,HSTACK(MAX,ARRAYTOTEXT,SUM),,0),1,1))
Great formula!! ✌or this:
=VSTACK({"Date","Description","Total Sales"},DROP(GROUPBY(SCAN(0,fPaintStoreF[Date]"",SUM),fPaintStoreF,HSTACK(MAX,ARRAYTOTEXT,SUM),,0),1,1))
and this , uses table's headers, single variable, entire table:
=LET(t,fPaintStoreF[#All],DROP(GROUPBY(SCAN(0,TAKE(t,,1)"",SUM),t,HSTACK(MAX,ARRAYTOTEXT,SUM),3,0),1,1))
Thanks, Software train!!!! I learned a lot from Excel Lambda, Viktor and you : ) : ) I have posted your formula in the download file.
@@Excelambda Even we can do it shorter ( remove ""):
=LET(t,fPaintStoreF[#All],DROP(GROUPBY(SCAN(0,TAKE(t,,1),SUM),t,HSTACK(MAX,ARRAYTOTEXT,SUM),3,0),1,1))
the reason "we" use "" is because it can handle also text, not only numbers , therefore, for robustness, a formula that can handle both consistent date formats as numbers or text or any other text (there are ways to do it even with mixed data, and "" handles all scenarios)
=LET(t,fPaintStoreF[#All],x,TAKE(t,,1),i,OR(ISNUMBER(x)),s,SUM,
DROP(GROUPBY(SCAN(0,x"",s),t,HSTACK(IF(i,s,CONCAT),ARRAYTOTEXT,s),3,0),1,1))
=> if main interest is only to find the shortest for 1 case only , this one is even shorter 😀
=LET(t,fPaintStoreF[#All],s,SUM,DROP(GROUPBY(SCAN(0,TAKE(t,,1),s),t,HSTACK(s,ARRAYTOTEXT,s),3,0),1,1))
🏆
Yes!!!!!!!!
I was gonna comment: “Come on, Mike, we’re not gonna be satisfied until you do it in a single cell formula”,…
and then I heard the last sentence. ;-)
I did the same! I'm probably going to try it myself before then. It's an addiction.
You know me very well, my friend : ) I try to not let the Team down!!!!!!
@@ricos1497 It is an awesome addiction : ) : )
@@excelisfunI went down the rabbit hole since my last reply, I couldn't resist. I split it into two, because I thought what we really needed is a reusable FILLDOWN function, which can then be used as necessary in the existing GROUPBY function. Of course, getting a FILLDOWN function to work on one column was relatively easy, but that's not good enough for the sweet dopamine hit of the Excel function addict. I came up with this FILLDOWN function that will work on a table of data*. It creates a table of row numbers using SCAN on each column of the table, then a table of filtered values that removes any blanks in each column, then does an index on the values table using the row numbers.
FILLDOWN
=LAMBDA(data,LET(c,COLUMNS(data),
seqC,SEQUENCE(c),
seqR,SEQUENCE(ROWS(data)),
FILLINDEX,LAMBDA(x,SCAN(0,x,LAMBDA(a,b, a+(b0)))),
fillIDs,REDUCE(0,seqC,LAMBDA(i,j,IF(j=1,FILLINDEX(INDEX(data,,j)),HSTACK(i,FILLINDEX(INDEX(data,,j)))))),
FILTERARRAY,LAMBDA(d,LET(f,FILTER(d,d0),IF(seqR>ROWS(f),0,f))),
filteredValues,REDUCE(0,seqC,LAMBDA(i,j,IF(j=1,FILTERARRAY(INDEX(data,seqR,j)),HSTACK(i,FILTERARRAY(INDEX(data,seqR,j)))))),
result,INDEX(filteredValues,fillIDs,SEQUENCE(,c)),
result)
)
and then group using:
=GROUPBY(DROP(FILLDOWN(fPaintStoreF),,-1),fPaintStoreF[Amount],SUM)
*It would be easy to add a second criteria to FILLDOWN for an array of column numbers to more closely mirror the PQ function, Obviously, the FILLDOWN option is great when you have more than one column missing values, which is quite common in some of the nonsense data I receive!
@@excelisfun my last reply not coming through yet, but in my rush to create a filldown function, I forgot what the actual question was! The actual final part should have read:
=LET(fD,FILLDOWN(fPaintStoreF[Date]),
hdr,fPaintStoreF[#Headers],
d,GROUPBY(fD,fPaintStoreF[Description],ARRAYTOTEXT,0,0),
s,GROUPBY(fD,fPaintStoreF[Amount],SUM,0,0),
result,VSTACK(hdr,HSTACK(d,TAKE(s,,-1))),
result)
using FILLDOWN function:
=LAMBDA(data,LET(c,COLUMNS(data),
seqC,SEQUENCE(c),
seqR,SEQUENCE(ROWS(data)),
FILLINDEX,LAMBDA(x,SCAN(0,x,LAMBDA(a,b, a+(b0)))),
fillIDs,REDUCE(0,seqC,LAMBDA(i,j,IF(j=1,FILLINDEX(INDEX(data,,j)),HSTACK(i,FILLINDEX(INDEX(data,,j)))))),
FILTERARRAY,LAMBDA(d,LET(f,FILTER(d,d0),IF(seqR>ROWS(f),0,f))),
filteredValues,REDUCE(0,seqC,LAMBDA(i,j,IF(j=1,FILTERARRAY(INDEX(data,seqR,j)),HSTACK(i,FILTERARRAY(INDEX(data,seqR,j)))))),
result,INDEX(filteredValues,fillIDs,SEQUENCE(,c)), result)
)
Hello Mike, can you film the dynamic array of sales in different dates with original currency which is being into another currency, blowing up on screen with Sumifs two columns, original currency and quoted sales currency amount?
I am struggling with making a formula work. I have a matrix of values and need to find the values in a column, based on the lookup being between 0-50, 50-100, 100-150 or 150-200. I've tried xlookup and index-match-match and get an "N/A" error. I love your videos. I cannot thank you enough for the knowledge you've shared and hope you can help.
If you can explain more precisely what the structure of the data is, where the values are in the data, and give me an example of what your are looking up and what the correct result should be, I bet I can help.
I am just unclear on what you mean by values in a column. Is it that at the head of each row lies the values 0,50,100,150,200 and so on?
@excelisfun It is a cost table for spa finishes (I'm a pool builder). The costs are based on two criteria; 1) type of finish and 2) spa square footage. There are 9 finish types and 4 square footage categories, so let's assume the table runs from A1:E10 with row 1 as a header. The first column is the finish type, the next has the costs for each type 151 sq. ft. I need the formula to lookup the row based on the finish type and the column based on spa square footage and return the value in the cell containing the cost.
@@excelisfun Did that description help you? Can you help me with how to make this work?
I could have took entire range in criteria in SUMIFS to make it spilled array.
Also I had to stop & think at B3 with =COUNTIFS(D$3:D3,">0") 😊
We're counting up sequentially (dragging down) and when Date is blank, it does not add 1 (number does not change);
therefore indicating the same group with the previous Date. Is this correct logic?
Thanks Mr. MG
Yes. Every row in any data set that you will analyze needs a marker in each row that says it belongs to the group.
at 2:00 - Please help 😢with Description Filter in J3
I don't understand the FILTER include part $B$3:$B$15=H3
when B3:B15 is not in the same fPaintStoreF Table.
Thanks Mr. MG
The range is the same number of rows as the table so the trues and falses filters the table by 2, then 3 and so on : )