Advanced Excel Functions | BYROW vs MAP vs SCAN vs REDUCE | Excel Off The Grid

[@DataVisualisation]

Explained briefly and concisely but very clearly and comprehensibly. If anyone has not understood it yet ...

[@ExcelOffTheGrid]

Thank you. I appreciate that feedback. 😁

[@Al-Ahdal]

The most comprehensive tutorial on a very important new functions.

[@ExcelOffTheGrid]

Thank you 😁

[@viktorasgolubevas2386]

Perfectly summarized and structured, as always. Thanks a lot! And a winner is... PIVOTBY :)

[@viktorasgolubevas2386]

actually, for this specific form of report, I would use =GROUPBY(Data[Item], Data[Value], ARRAYTOTEXT,,0) or =GROUPBY(Data[Item], Data[Value], LAMBDA(_,TEXTJOIN(" | ",,_)),,0) ...and of course, don't forget to sort the data

[@ExcelOffTheGrid]

Yes, that would also be a solid choice for this scenario.

[@tibibara]

I think, this is the most comprehensive and clear comparative tutorial about these 4 LAMBDA helpers. Thanks!

[@ExcelOffTheGrid]

Thank you. I appreciate that. 😁

[@akulanarayana5284]

Wow what a presentation which is very important.🎉👍👏👌

[@pamphlex]

This is the current best video about all those functions in excel.

[@ExcelOffTheGrid]

Thank you, that is very kind of you to say. 😁

[@chilaw2004]

“Let’s go!”? What happened to “Let’s get started!”? New functions, new catchphrases - same great explanations from Mark.

[@ExcelOffTheGrid]

Ha ha ha - a little change to keep your on your toes 🤣

[@stevereed5776]

Very nice Mark, thank you. I would like to see more videos on SCAN and REDUCE

[@ExcelOffTheGrid]

You are right, there aren’t many resources about those functions around.

[@Excelambda]

Great video!! TJ can ignore empty so we can exclude filter: =MAP(F6#,LAMBDA(r,TEXTJOIN("|",,IF(Data[Item]=r,Data[Value],"")))) groupby alternative that looks cool: =GROUPBY(Data[Item],"|"&Data[Value]&"|",CONCAT,,0) Makes more sense and it's very easy to do scanning running totals "byrow" not cumulative. BYROW can not spill more than 1 val/row, REDUCE with VSTACK rows is the slowest method. We can still use SCAN with an accumulator that sets to 0 at the beginning of each row For an array "a" of x rows and y columns running tot for each row : =SCAN(0,SEQUENCE(x,y),LAMBDA(v,i,LET(m,MOD(i-1,y),IF(m,v,0)+INDEX(a,QUOTIENT(i-1,y)+1,m+1)))) for your data, clean running tot 1.2* byrow: =SCAN(0,SEQUENCE(3,4),LAMBDA(v,i,LET(m,MOD(i-1,4),IF(m,v,0)+1.2*INDEX(J6#,QUOTIENT(i-1,4)+1,m+1)))) returns: 74.4 154.8 224.4 328.8 80.4 158.4 234 319.2 96 210 272.4 370.8

[@ExcelOffTheGrid]

That is next level - great work. ✅

[@extraktAI]

This is great, thanks! 👏

[@ExcelOffTheGrid]

Thank you. Glad you like it!

[@ennykraft]

Great video. Thank you! I rarely use these functions so that was really helpful. Btw this scenario is the reason why I'm head of heels with GROUPBY and PIVOTBY. First example: =SUBSTITUTE(GROUPBY(Data[Item],Data[Value],ARRAYTOTEXT,0,0),",","|") For the cross tabulated array, I'd use =SUMIFS(Data[Value],Data[Item],I6#) For PIVOTBY I'd first create my array in I5 with this function.=PIVOTBY(Data[Item],Data[Quarter],Data[Value],SUM,0,0,,1) And then =IFERROR(CHOOSECOLS(I5#,6)*1.2,"") Of course you can replace I5# with the PIVOTBY (PS I'm German and hopefully I didn't mess up the functions and the commas)

[@ExcelOffTheGrid]

Nice options. Good work. 👍

[@westleyempeigne6541]

Thanks Mark, that’s looks clearer now, what each one does and the differences between them!

[@GiovanniGirelli-w3z]

Great video! Very helpful!

[@chrism9037]

Very cool thanks Mark!

[@ExcelOffTheGrid]

Thank you. I’m glad you like it.

[@DinoDelight]

Another great video, love how your exploring Lambda more

[@ExcelOffTheGrid]

I’m glad you’re enjoying it. I’m sure there will be more to come!

[@FabricioLennar]

Great content

[@jerrydellasala7643]

Great video! These are great functions that aren't covered enough! It's amazing how much MS is doing to Excel! I'm on the Beta Insider edition (Version 2409 Build 16.0.18011.20000), and when I was following along in the BYROW section around 5:17, when I typed the comma in the "=BYROW(J6#," functions popped up the way they do in the GROUPBY and PIVOTBY functions. I'm able to use the formula "=BYROW(J6#,SUM)*1.2" without having to use a LAMBDA! No such luck with the other functions, but something to look forward to!

[@ExcelOffTheGrid]

This was introduced at the same time as GROUPBY/PIVOTBY. If a function has a single argument, you can use “Eta Reduction” Instead of LAMBDA(x,SUM(x)) you can just use SUM. It is syntax sugar. In the background Excel converts it to a LAMBDA for you and the applies the calculation. Only works with single argument functions, for multiple functions you have to use the full syntax.

[@viktorasgolubevas2386]

Why "no luck"? =MAP(J6#,SUM)*1.2 =SCAN(,J6#,SUM)*1.2 =REDUCE(,J6#,SUM)*1.2 result as Mark's :) btw, in LAMBDA versions of these 3 functions, we do not need summing single cell since SUM(c)=c =SCAN(0, J6#, LAMBDA(a, v, a + v*1.2)) moreover, in Scenario #2, MAP is equivalent to =J6#*1.2 and REDUCE to =SUM(J6#)*1.2

[@Rice0987]

Simply great lesson! Thank you, Mark!😊 Recently I've started to learn these questions, but your video just get in time! Now I'll just apply it on my daily work! THANKS A LOT!🤗

[@ExcelOffTheGrid]

Thanks. 😁 Great tactic, instantly applying in the real world, helps to embed the knowledge.

[@Rice0987]

@@ExcelOffTheGrid unfortunatelly, my filters and xlookups return me CALC error when I applying byrow function with lambda like in video.🤔

[@Rice0987]

I've found the answer: ARRAYTOTEXT or another text join function.😊 Thanks!

[@spen2431]

Mind blown... Love it ! 🤯🤯 Thanks for sharing

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@safuwanch]

Super quality of information🎉🎉❤

[@ExcelOffTheGrid]

Thank you so much 😁

[@AnshulMarele-gbaa]

Hi, you always post very informative videos. I have an Excel365 file which data from the odbc table which updates automatically in scheduled intervals, how can I sync that Excel data into Google Sheets with auto updates?

[@ExcelOffTheGrid]

Sorry - Google Sheets isn’t my bag. Can’t help you there.

[@RichardJones73]

Made my head hurt, but at least I understood it lol

[@ExcelOffTheGrid]

LAMBDAs are a tricky concept. But keep working at it; it’s worth it.

[@DaveIsAtWork-Really]

The different functions you highlight are interesting, but not something I think I can directly begin using. I am more interested in the 'Scenario #2' dynamic array row, columns, and single formula, since that's similar to how I need to summarize and present a large subset of my reporting. But is there a way to HSTACK or VSTACK a "Total" to both the horizontal and vertical arrays, and then have each row and column summed?

[@DaveIsAtWork-Really]

Here are the three formulas that I bashed out that gets the job done: rows with totals, columns with totals, and a final grand total. I'm not likely to ever use it as is, but it was an interesting exercise. Any thoughts though on if it could incorporate BYROW, MAP, SCAN, or REDUCE as used in the video? Column headers with sorted unique dates (text-formatted) and "TOTAL": =HSTACK(TRANSPOSE(TEXT(SORT(UNIQUE(tblData[DATE])),"mm/dd/yy")),"TOTAL") Row labels with unique customer numbers and "TOTAL" (with additional attributes for sorting & display): VSTACK(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),{"TOTAL","","",""}) And the data array, with totals (broken apart for easier reading): =IFERROR( LET( Dates,TRANSPOSE(SORT(UNIQUE(tblData[DATE]))), Custs,CHOOSECOLS(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),4), VSTACK( HSTACK( SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs,tblData[DATE],Dates), SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs)), SUMIFS(tblData[AMOUNT],tblData[DATE],Dates))), SUM(tblData[AMOUNT]))

[@rufinojr54]

thanks sir but could i apply any of the following into this: i have an array, b2#, range is from b1 to f2. b1 to f1 contains 100,101,102,103,104. in row 2 is aaa, aaa, aab, aac, aac. i have another array, a3#, range is from a3 to a10 which contains outlet1 to outlet8. my 3rd array is, b3#, values bought by outlets under b1 to f1. in my 4th array, h2#, from h2 to j2, contains aaa, aab, aac. how do i sum values from b3# of my outlets in a3# under h2#? what formula should i use, byrow, bycol, map, scan, reduce, to get the result that i want?

[@ExcelOffTheGrid]

If I understand your description correctly, you could use the following. It uses a REDUCE and a BYROW. =DROP(REDUCE({""},H2#,LAMBDA(a,c,HSTACK(a,BYROW(FILTER(B3#,ISNUMBER(XMATCH(B2#,c))),SUM)))),,1) There maybe a better method, but this is first idea I had.

[@rufinojr54]

@@ExcelOffTheGrid IT WORKED PERFECTLY!!! just modified B2# to DROP(B1#,1). Thanks a lot sir. Anyone may try this amazing formula.

[@nkoichev]

These functions are part of Microsoft 365 subscriptions, if you're using a standalone version of Excel like Excel 2019 or earlier, these functions won't be available.

[@ExcelOffTheGrid]

Yes, very true.

[@ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0220 BYROW MAP SCAN REDUCE Comparison

[@Quidisi]

So MAP should have been named BYCELL

[@ExcelOffTheGrid]

That would have been a sensible name to go for. Because “map” exists in other programming languages as a way to loop over the values in an array, then it also makes sense if you know those languages.