Yay! Crazy Array formulas are back!... because they never left! :-) And you, Mike, are the Master! Wow! The result is awesome! And so is that formula! BTW: in this case I’ll take the pivot table any day of the week.Just way simpler. That’s not to say that I don’t love the new calc engine: I wouldn’t want to live without it...
Yes, most of us will use a PivotTable, but I have heard of a few people are using formulas just like this for reports and invoicing that they say is perfect!
@@excelisfun Mike/Geert, I can see the attractiveness of something like this. The thing about LET functions is that they are reusable. For this one, just copy it into a workbook, change the source of the drop down lists, the source data and the column and you're there. If you're really smart you can even add subtotals. Formatting is obviously the biggest issue, but then it isn't that great on pivot tables. However, the most amazing thing for me is what it shows can be done. I've done LET functions for a few things now, and each time I've tried to make them as close to reusable as possible (and suceeded on most occasions). If we can write a function that always returns a table with row and column totals based on a data source, a column to SUM, a row and a column field, then Excel can do it. If Excel can do it, and it's repeatable and provable, then its really then up to Microsoft to provide that function as standard (or we can certainly build that case). For example, I've built a SPLIT() function for something I was doing, and a TEXTTOCOLUMNS() function. Now we have a PIVOT() function, and I have done an UNPIVOT() function (sort of) all in LET that can be re-used just by changing the initial variables as you would any other function. The LET function simply takes in variables and performs an algorithm. There is no reason that these can't be shared, proved and then voted on for microsoft to add to it's growing list. I vote for PIVOT(DataSource, ColumnField, RowField, ValueField, IncludeHeaders, IncludeTotals) as a function! Which we can now do based on Mike's function, or my one I added elsewhere in the comments! Easy!
Wow!! Blown away by this one! Thanks for a master class on how to bend Excel Functions and Formulas to do your bidding. Absolute Monday Madness!! Thumbs up!!
Thank you for a lovely challenge. Here's single cell formula I was able to come up with for this problem: =LET( VR,INDIRECT("fSales["&K2&"]"), UVR,SORT(UNIQUE(VR)), VC,INDIRECT("fSales["&K3&"]"), UVC,SORT(UNIQUE(VC)), Sum,SUMIFS(fSales[Revenue],VR,UVR,VC,TRANSPOSE(UVC)), TR,BYROW(Sum,LAMBDA(row,SUM(row))), TC,BYCOL(Sum,LAMBDA(col,SUM(col))), Total,REDUCE(0,Sum,LAMBDA(i,cell,i+cell)), RHeaders,EXPAND(UVR,COUNTA(UVR)+1,,"Total"), CHeaders,TRANSPOSE(EXPAND(UVC,COUNTA(UVC)+1,,"Total")), Corner,K2&"/"&K3, Output,HSTACK(VSTACK(Corner,RHeaders),VSTACK(CHeaders,HSTACK(VSTACK(Sum,TC),VSTACK(TR,Total)))),Output)
Mike just awesome thanks for doing the video you are amazing :) Not only is the calculation more efficient but if you need to amend a variable later you only need to do it once! 👍
I HATE that F9 does not work, and that the current version does not allow variables in drop down. It makes the LET function in the current version almost useless. It is just too hard with those bugs.
@@excelisfun Sometimes it is easier to create the old way then convert to use Let via search and replace. Let can make some formulas so much easier to review later that it is worth the double-effort. PS my brain is still aching from following this video lol
Hey Mike, I remember that a few videos ago you talked about this formula to remove numbers from a cell. it's not the best formula ever, but I think this might work. You may use this formula {=TEXTJOIN("",TRUE,IFERROR(IF(ISNUMBER(NUMBERVALUE(MID(A5,ROW(INDIRECT("1:100")),1))),"",MID(A5,ROW(INDIRECT("1:100")),1)),""))} this will remove all numbers. However, if you'd like to remove text and leave numbers, you may use this one {=TEXTJOIN("",TRUE,IFERROR(MID(A5,ROW(INDIRECT("1:100")),1)+0,""))}
I’ve been using LET in almost all my formulas lately. It’s so helpful. Great video. I always name the last formula/calculation as Result/RES for clarifications I have noticed versions beyond 13001.20266 on build 2006 do not show the variable names the autocomplete functionality like named ranges, formulas, and table names do. I haven’t checked build 2007 version 13029.20308 yet but the beta version of 2007 a few weeks didn’t work either
ExcelIsFun what version do you have? The variables weren’t displayed when LET was first released. That functionality was added in a later version. Not sure if it’s been taken out or temporarily disabled. I wish it had color like table names and names ranges. Also I wish table references from other sheets or workbooks had color too
ExcelIsFun I can confirm the LET function does not show variable names in the autocomplete in build 2007 version 13029.20308. Just checked. Reverting back to build 2006 version 13001.20266 for now
Mike, I am making amazing use of this lesson! Thank you! Question: can a third criteria selector be added? Specifically, include the date column as criteria, so User can do everything done here, but limit the report to the date range selected (within the LET function or from with in the same cell as the LET function)?
01:09 .... what!! Mike I am honored to have inspired for this video to be made! WOW!! I am still waiting for my Excel version to have =LET() .... I am not part of the Insider Program becuase of compatability issues I had before ... : (
Thanks, Mike! I further wanted the transposed column headers to be a part of the single cell report. I was able to append the two (column headers and the output below) by just joining the two arrays with a colon (K2#:K3#) in a separate cell! However, if I try to put that in LET or any other formula, like CHOOSE({1:2},firstarray#,secondarray#), it is not appending without errors. Your valuable thoughts please!
Boss, how to use VSTACK, HSTACK simultaneously to bring in all row headers and column headers in this video and use single formula. Kindly guide on this. Thank you in advance.
Can DGET be used to return multiple values based on a cell value from the same or another worksheet. ( One to many) Lets say we have companies and would like to see the list ( records) of all employees of a selected company with names, titles, email addresses etc.? Also is it possible that these records can be edited/updated right after showing up/ filtering? If not, what would you recommend to use?
At the risk of sounding smug, I think I can go one better, by including the whole table in one formula: =LET(rowVar,K2, colVar,K3, sumCol,fSales[Revenue], tblHdr,rowVar&"/"&colVar, xValsAll,XLOOKUP(colVar,fSales[#Headers],fSales[#Data]), yValsAll,XLOOKUP(rowVar,fSales[#Headers],fSales[#Data]), xHdrs,SORT(UNIQUE(xValsAll)), yHdrs,SORT(UNIQUE(yValsAll)), cntX,ROWS(xHdrs)+2, cntY,ROWS(yHdrs)+2, i,SEQUENCE(cntY,cntX), x,IF(MOD(i,cntX)=0,cntX,MOD(i,cntX)), y,ROUNDUP(i/cntX,0), xVal,INDEX(xHdrs,x-1,1), yVal,INDEX(yHdrs,y-1,1), results,SWITCH(TRUE,x*y=1,tblHdr,((x
To add some explanation, the trick is to use a 2 dimensional sequence rather than just a single column, which represents the table with a sequence of numbers. If you try stepping through the above formula, when you return "i" with a row variable of Product and column variable of Region, you get a 4 row, 6 column sequence table, which is what you'd expect as there are 4 products, plus 2 columns for header and totals. There are 2 Regions, plus 2 for header/totals. That's your table mapped, but with a number sequence 1-24 instead of values. As it's dynamic, you need to know what your x and y coordinates are for each instance of "i" and return the value for that coordinate. The MOD formula will get the x value (column number) and the ROUNDUP the y value (row number) (you can test these both in the LET function to prove they return correctly. Finally, use the SWITCH formula to define what is returned for each position in the sequence table. For example ((x
And for more information! Here is a LET function that determines where you are in a table based on the number of rows and columns. Simply change the maxCol,6+2 and maxRow,2+2 to 4+2 and 3+2 or whatever to show the movement of the pivot table (the +2 is to add a column/row for both header and total). It's quite a good illustration, and you would simply add in your data source XLOOKUPS and the SUMIFS formulas in place of Values and Row Total sections in the SWITCH formula: =LET(maxCol,6+2, maxRow,2+2, i,SEQUENCE(maxRow,maxCol), x,IF(MOD(i,maxCol)=0,maxCol,MOD(i,maxCol)), y,ROUNDUP(i/maxCol,0), result,SWITCH(TRUE,x*y=1,"Header",((x
@@ricos1497 Really Great Work! The trick is..you dnt need 2 dim. seq arrays at all. Excel fills the gaps. for example put this in any cell and check the result =SWITCH(TRUE,SEQUENCE(,3)=1,{1;2;3;4},SEQUENCE(,3)=3,{"a";"b";"c";"d"}). See? 2 dim array 4 by 3 with the left and right column added using one dim sequences. The same way you can do, top row , bottom row etc. For TRUE you leave the core array that fills itself, no conditions needed, so for all conditions for creating the perimeter of array , left and right column, top and bottom row, only one dim sequence array You used SWITCH , I used IFS, more ore less the same. I used very simple 2 dim sequence Only for the corners.
@@Excelambda yes, great suggestion. I always forget about IFS() too, that'd be nicer than switch. I'll have to get the laptop out again and check out your idea. Fantastic.
@@Excelambda I've had a quick test of your suggestion, and I understand it, but I'm not entirely sure how to apply it to the example in the video. Do you have your solution/formula? I'd be quite interested to see it in action.
Hey how are you today sir? can u recomended a video where i can make searcheble drop down list. i have 4000 items and idea was to make a search bar on top few cels and under is all my product that i selling. but i want when customer tipe few ford of items that he looking for under the search bar made a list of all items that contain that leather.. i hope you understand my bad english. chears
Very interesting, how do I create a power query for a table that contains numbers and text? The numbers contain an employee number that has a letter in it which cannot be removed and it's driving me nuts!
@@excelisfun im trying :) I also read upon how to get MVP :) and weirdly enough I do contribute on ExcelForum.pl website, I teach excel team I work with in my work, and now here i I find a way to help. Maybe even I couldcontribute to your videos somehow :) (altho on't know what I could do :))
Help!!! Do you have a video with retention codes for a warehouse. Example code 94-1 will add 2 years to a box received in 2014 making the destruction date 2016. Also I have over 300 codes with same/different years for each.
I remember your old formula without XLOOKUP. Now it is easier with XLOOKUP, and it occurred to me to put "*" instead of "Total" and then summing up is very simple. formula for list for data from 1528: =IF(SEQUENCE(,COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0))))+1)>COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0)))), "*",TRANSPOSE(SORT(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0)))))) And sum: =SUMIFS(fRevenue5[Revenue],XLOOKUP(I4,fRevenue5[#Headers],fRevenue5),H10#,XLOOKUP(I5,fRevenue5[#Headers],fRevenue5),I9#) P.S. I use conditional formatting to show "Total" instead of "*"
Is anyone able to give me a hand with a similar topic, I cannot quite figure out the syntax. Say for instance I have a table like this: Plant | Material | Q1 Price | Q2 Price | Q3 Price | Q4 Price | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | I would like the result of my new array using Let to Look like this: Plant | | Q1 | Q2 | Q3 | Q4 | I was able to use the basic ideas in this video to write something like this =LET( List,UNIQUE(Forecast[Plant]), REVCOL,Forecast[Jan], REVCOL2,Forecast[Feb], TEST,SUMIFS(REVCOL,Forecast[Plant],List), CHOOSE({1,2},List,TEST)) This results in a table Plant | Jan which correctly summarizes the January volumes by plant -- Now it is time to make a more complex calculation for instance I would normally use: =SUM(Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=$A1)) using a helper unique list to allow me to summarize what is essentially a sum product function, but I am trying to make that one of the calculations in my LET, such that my end result can be the following, allowing me to control a summary table layout: =LET( List,Unique(Forecast[Plant]) Q1,Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=List), Q2,Filter((Forecast[Apr]+Forecast[May]+Forecast[Jun])*Forecast[Q2],Forecast[Plant]=List), Q3,Filter((Forecast[Jul]+Forecast[Aug]+Forecast[Sep])*Forecast[Q3],Forecast[Plant]=List), Q4,Filter((Forecast[Oct]+Forecast[Nov]+Forecast[Dec])*Forecast[Q4],Forecast[Plant]=List), Choose({1,2,3,4,5,6},Plant,"",Q1,Q2,Q3,Q4)) Appreciate the help in advance here
No, no, no..... !!!!! Of course you have talent for Excel : ) : ) This sort of formula is at the outer extreme. Few people do this sort of thing. Forget this sort of stuff and just have fun with Excel that way you have fun with Excel, Kasia : ) : ) : ) : )
I used this one :) =LET(a,SORT(UNIQUE(XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer))),b,TRANSPOSE(SORT(UNIQUE(XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer)))),c,SUMIFS(fSalesAnswer[Revenue],XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer),a,XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer),b),d,BYCOL(c,LAMBDA(q,SUM(q))),e,BYROW(c,LAMBDA(r,SUM(r))),VSTACK(HSTACK(K2&"/"&K3,b,"Total"),HSTACK(a,c,e),HSTACK("Total",d,SUM(d))))
You are the best Excel teacher I have seen in my life
Glad to help : )
Topics:
1. (00:00) Introduction.
2. (00:33) Why LET is useful.
3. (01:25)Data Validation for variable cells.
4. (01:39) Dynamic Report Label.
5. (02:02) Row Variable Formula.
6. (05:03) Column Variable Formula.
7. (05:39) Single Cell Report Formula.
8. (14:09) Summary and Video Links.
Yay! Crazy Array formulas are back!... because they never left! :-)
And you, Mike, are the Master! Wow! The result is awesome! And so is that formula!
BTW: in this case I’ll take the pivot table any day of the week.Just way simpler.
That’s not to say that I don’t love the new calc engine: I wouldn’t want to live without it...
Yes, most of us will use a PivotTable, but I have heard of a few people are using formulas just like this for reports and invoicing that they say is perfect!
Well Mike, I “somehow” feel that those people owe you a debt of gratitude. Big time. :-)
@@GeertDelmulle I own them too - since I originally learned this from David Milbrandt : ) : ) Go Team!!!!!!
@@excelisfun Mike/Geert, I can see the attractiveness of something like this. The thing about LET functions is that they are reusable. For this one, just copy it into a workbook, change the source of the drop down lists, the source data and the column and you're there. If you're really smart you can even add subtotals. Formatting is obviously the biggest issue, but then it isn't that great on pivot tables. However, the most amazing thing for me is what it shows can be done. I've done LET functions for a few things now, and each time I've tried to make them as close to reusable as possible (and suceeded on most occasions). If we can write a function that always returns a table with row and column totals based on a data source, a column to SUM, a row and a column field, then Excel can do it. If Excel can do it, and it's repeatable and provable, then its really then up to Microsoft to provide that function as standard (or we can certainly build that case). For example, I've built a SPLIT() function for something I was doing, and a TEXTTOCOLUMNS() function. Now we have a PIVOT() function, and I have done an UNPIVOT() function (sort of) all in LET that can be re-used just by changing the initial variables as you would any other function. The LET function simply takes in variables and performs an algorithm. There is no reason that these can't be shared, proved and then voted on for microsoft to add to it's growing list.
I vote for PIVOT(DataSource, ColumnField, RowField, ValueField, IncludeHeaders, IncludeTotals) as a function! Which we can now do based on Mike's function, or my one I added elsewhere in the comments! Easy!
I was blown away by this!!! Thank you. I took a step further by using sort by to put the most relevant rows and columns at the top left of the report!
Where was this video 2 years ago?
Mind-blowing stuff. Thanks a lot 👍👍👍
LET was not around two years ago.
Can’t wait to get this in my MS 365. Nice video Mike!!
I can't wait either : ) : )
Wow!! Blown away by this one! Thanks for a master class on how to bend Excel Functions and Formulas to do your bidding. Absolute Monday Madness!! Thumbs up!!
It is wild madness : ) Glad it was mad for you, Wayne : )
WOW Mike, I need to watch the video at least 10 times
I am glad you like it, Sevag!!!
Fortunately LET exists ;). Thumbs up!
Oh my God.This video should be Labeled as How to Make Pivot Table without using pivot table.Super Duper Awesome video.
That is a good idea : )
Just....WOW! NIce job Mike.
Glad you like it, Michael !!!!
Brilliant!
Thanks, Mike.
You are welcome! Here is a newer video with the new functions VSTACK and HSTACK: ruclips.net/video/17U8_6besyI/видео.html
WOW! just WOW!...Thanks Mike.
Can't wait to have the LEN() function soon!
I can't wait for you to get LET, either : )
Wow ... that's amazing Mike ....every difficult solution is easy with you .
Glad I can help with making it more easy : )
Thank you for a lovely challenge. Here's single cell formula I was able to come up with for this problem:
=LET(
VR,INDIRECT("fSales["&K2&"]"),
UVR,SORT(UNIQUE(VR)),
VC,INDIRECT("fSales["&K3&"]"),
UVC,SORT(UNIQUE(VC)),
Sum,SUMIFS(fSales[Revenue],VR,UVR,VC,TRANSPOSE(UVC)),
TR,BYROW(Sum,LAMBDA(row,SUM(row))),
TC,BYCOL(Sum,LAMBDA(col,SUM(col))),
Total,REDUCE(0,Sum,LAMBDA(i,cell,i+cell)),
RHeaders,EXPAND(UVR,COUNTA(UVR)+1,,"Total"),
CHeaders,TRANSPOSE(EXPAND(UVC,COUNTA(UVC)+1,,"Total")),
Corner,K2&"/"&K3,
Output,HSTACK(VSTACK(Corner,RHeaders),VSTACK(CHeaders,HSTACK(VSTACK(Sum,TC),VSTACK(TR,Total)))),Output)
Holy Amazing Excel Functions, Batman!! That was FUN!
You are batman welcome!
Mike just awesome thanks for doing the video you are amazing :) Not only is the calculation more efficient but if you need to amend a variable later you only need to do it once! 👍
Yes, that is so true. In fact I did exactly that multiple times as I created the formula : ) : )
That was a great use of new Excel functions! However, Microsoft must return F9 in Let Function to quickly check intermediate results
I HATE that F9 does not work, and that the current version does not allow variables in drop down. It makes the LET function in the current version almost useless. It is just too hard with those bugs.
@@excelisfun Sometimes it is easier to create the old way then convert to use Let via search and replace. Let can make some formulas so much easier to review later that it is worth the double-effort. PS my brain is still aching from following this video lol
@@henryg5735 That is true.
Hey Mike, I remember that a few videos ago you talked about this formula to remove numbers from a cell. it's not the best formula ever, but I think this might work. You may use this formula {=TEXTJOIN("",TRUE,IFERROR(IF(ISNUMBER(NUMBERVALUE(MID(A5,ROW(INDIRECT("1:100")),1))),"",MID(A5,ROW(INDIRECT("1:100")),1)),""))} this will remove all numbers. However, if you'd like to remove text and leave numbers, you may use this one {=TEXTJOIN("",TRUE,IFERROR(MID(A5,ROW(INDIRECT("1:100")),1)+0,""))}
Thanks for the cool formulas : )
Excelent, thanks EXCELISFUN
You are welcome!!!!
Amazing piece of excel skill!
Yes indeed. Thanks, Hassan!!!
Boom!WOW Completely Blown Away What An Awesome Formula...Thank You Mike :)
Boom is for this video boomeranged back in new form : ) : )
This is amazing Mike. I need the LET function in my MS Excel 365. Thanks for sharing
You are welcome for the share, Nono!!!
Wow Mike, this is mnd blowing, thansk for sharing, you're the best 👍👍👍
You are welcome, Stephen!
Absolutely outstanding Mike, thank you 👍
You aer welcome, Paul : ) : )
Good one. Looks like a very complex formula.
VERY. Not necessary 99% of the time, but for the 1% it is perfect : )
This is amazing mike
Glad it is amazing for you, kishor!!!
Thanks Mike. This is a MasterPiece!!! FUN FUN FUN : ) : )
Glad you like the formula FUN, FUN, FUN!!!!
Thanks Mike. This video is really great and useful.
Glad you like it, K D!!!!
Thanks Mike!
You are welcome, David!!!!
I’ve been using LET in almost all my formulas lately. It’s so helpful. Great video. I always name the last formula/calculation as Result/RES for clarifications
I have noticed versions beyond 13001.20266 on build 2006 do not show the variable names the autocomplete functionality like named ranges, formulas, and table names do. I haven’t checked build 2007 version 13029.20308 yet but the beta version of 2007 a few weeks didn’t work either
Yes, my version is not showing variable name, although earlier versions did show it.
ExcelIsFun what version do you have? The variables weren’t displayed when LET was first released. That functionality was added in a later version. Not sure if it’s been taken out or temporarily disabled. I wish it had color like table names and names ranges. Also I wish table references from other sheets or workbooks had color too
ExcelIsFun I can confirm the LET function does not show variable names in the autocomplete in build 2007 version 13029.20308. Just checked. Reverting back to build 2006 version 13001.20266 for now
@@patrickschardt7724 That is smart to revert back : ) : )
Great Mike
Glad you like it, Amit!!!
Just Wow❗️❗️❗️Amazing 👍
Glad it is amazing for you, Davor : )
Mike, I am making amazing use of this lesson! Thank you! Question: can a third criteria selector be added? Specifically, include the date column as criteria, so User can do everything done here, but limit the report to the date range selected (within the LET function or from with in the same cell as the LET function)?
💖💚💗💙💟
Thanks for the love : )
@@excelisfun Thank you Sir Mike.
01:09 .... what!! Mike I am honored to have inspired for this video to be made! WOW!! I am still waiting for my Excel version to have =LET() .... I am not part of the Insider Program becuase of compatability issues I had before ... : (
Go Team!!!!!!!
Yet, since I don´t have the LET(), I would have indent the formula this way. (forgive me for my "pendantic-ness"!!)
For the row header:
=LET(
RowVariableUniqueList,
UNIQUE(
XLOOKUP(
K2,
fSalesAnswer[[#Headers], [Customer]:[Product]],
fSalesAnswer[[Customer]:[Product]] )
),
CountRowVariableUniqueList,
ROWS(RowVariableUniqueList),
IF(
SEQUENCE( CountRowVariableUniqueList+1 ) > CountRowVariableUniqueList,
"Total",
SORT(RowVariableUniqueList)
)
)
For the column header:
=LET(
ColumnVariableUniqueList,
UNIQUE(
XLOOKUP(
K3,
fSalesAnswer[[#Headers], [Customer]:[Product]],
fSalesAnswer[[Customer]:[Product]]
)
),
CountColumnVariableUniqueList,
ROWS( ColumnVariableUniqueList ),
TransposedColumnVariableUniqueList,
TRANSPOSE( SORT( ColumnVariableUniqueList ) ),
IF(
SEQUENCE( , CountColumnVariableUniqueList+1)> CountColumnVariableUniqueList,
"Total",
TransposedColumnVariableUniqueList
)
)
And the for magic formula!!!:
=LET(
Records, fSalesAnswer[[Customer]:[Product]],
FieldNames, fSalesAnswer[[#Headers],[Customer]:[Product]],
RevColumn, fSalesAnswer[Revenue],
RowUniqueList, J7#,
ColumnUniqueList, K6#,
CountRowUniqueList, ROWS( RowUniqueList ),
CountColumnUniqueList, COLUMNS( ColumnUniqueList ),
LookupColumnForRowVariable, XLOOKUP( K2, FieldNames, Records),
LookupColumnForColumnVariable, XLOOKUP( K3, FieldNames, Records),
IF(
RowUniqueList & ColumnUniqueList="TotalTotal",
SUM(RevColumn),
IF(
SEQUENCE(, CountColumnUniqueList) > CountColumnUniqueList-1,
SUMIFS( RevColumn, LookupColumnForRowVariable, RowUniqueList ),
IF(
SEQUENCE( CountRowUniqueList) > CountRowUniqueList-1,
SUMIFS( RevColumn, LookupColumnForColumnVariable, ColumnUniqueList ),
SUMIFS(
RevColumn,
LookupColumnForColumnVariable, ColumnUniqueList,
LookupColumnForRowVariable, RowUniqueList
)
)
)
)
)
Funny fact: I don´t write code or develop VBA, I just like seeing formulas written this way to rapidly spot where the issue is. :P
Amazing
Glad it is amazing for you. P.S. I still think I like the old way better...
Thanks, Mike! I further wanted the transposed column headers to be a part of the single cell report. I was able to append the two (column headers and the output below) by just joining the two arrays with a colon (K2#:K3#) in a separate cell! However, if I try to put that in LET or any other formula, like CHOOSE({1:2},firstarray#,secondarray#), it is not appending without errors. Your valuable thoughts please!
Boss, kindly make a video, solving the same by using VSTACK, HSTACK..... i.e. using single formula. Thanks in Advance,
Total Crazy
Wow, amazing
Glad it is amazing for you, MD Tech!!!
Boss, how to use VSTACK, HSTACK simultaneously to bring in all row headers and column headers in this video and use single formula. Kindly guide on this. Thank you in advance.
Can DGET be used to return multiple values based on a cell value from the same or another worksheet. ( One to many) Lets say we have companies and would like to see the list ( records) of all employees of a selected company with names, titles, email addresses etc.? Also is it possible that these records can be edited/updated right after showing up/ filtering? If not, what would you recommend to use?
Hi, I think the “TotalTotal sum() “gives the unconditional total amount, not specific to “cross table’s” condition
The total total and cross tab total are the same when you have the row area and column area unique list formulas : )
At the risk of sounding smug, I think I can go one better, by including the whole table in one formula:
=LET(rowVar,K2,
colVar,K3,
sumCol,fSales[Revenue],
tblHdr,rowVar&"/"&colVar,
xValsAll,XLOOKUP(colVar,fSales[#Headers],fSales[#Data]),
yValsAll,XLOOKUP(rowVar,fSales[#Headers],fSales[#Data]),
xHdrs,SORT(UNIQUE(xValsAll)),
yHdrs,SORT(UNIQUE(yValsAll)),
cntX,ROWS(xHdrs)+2,
cntY,ROWS(yHdrs)+2,
i,SEQUENCE(cntY,cntX),
x,IF(MOD(i,cntX)=0,cntX,MOD(i,cntX)),
y,ROUNDUP(i/cntX,0),
xVal,INDEX(xHdrs,x-1,1),
yVal,INDEX(yHdrs,y-1,1),
results,SWITCH(TRUE,x*y=1,tblHdr,((x
To add some explanation, the trick is to use a 2 dimensional sequence rather than just a single column, which represents the table with a sequence of numbers. If you try stepping through the above formula, when you return "i" with a row variable of Product and column variable of Region, you get a 4 row, 6 column sequence table, which is what you'd expect as there are 4 products, plus 2 columns for header and totals. There are 2 Regions, plus 2 for header/totals.
That's your table mapped, but with a number sequence 1-24 instead of values. As it's dynamic, you need to know what your x and y coordinates are for each instance of "i" and return the value for that coordinate. The MOD formula will get the x value (column number) and the ROUNDUP the y value (row number) (you can test these both in the LET function to prove they return correctly. Finally, use the SWITCH formula to define what is returned for each position in the sequence table. For example ((x
And for more information! Here is a LET function that determines where you are in a table based on the number of rows and columns. Simply change the maxCol,6+2 and maxRow,2+2 to 4+2 and 3+2 or whatever to show the movement of the pivot table (the +2 is to add a column/row for both header and total). It's quite a good illustration, and you would simply add in your data source XLOOKUPS and the SUMIFS formulas in place of Values and Row Total sections in the SWITCH formula:
=LET(maxCol,6+2,
maxRow,2+2,
i,SEQUENCE(maxRow,maxCol),
x,IF(MOD(i,maxCol)=0,maxCol,MOD(i,maxCol)),
y,ROUNDUP(i/maxCol,0),
result,SWITCH(TRUE,x*y=1,"Header",((x
@@ricos1497 Really Great Work! The trick is..you dnt need 2 dim. seq arrays at all. Excel fills the gaps. for example put this in any cell and check the result =SWITCH(TRUE,SEQUENCE(,3)=1,{1;2;3;4},SEQUENCE(,3)=3,{"a";"b";"c";"d"}). See? 2 dim array 4 by 3 with the left and right column added using one dim sequences.
The same way you can do, top row , bottom row etc. For TRUE you leave the core array that fills itself, no conditions needed, so for all conditions for creating the perimeter of array , left and right column, top and bottom row, only one dim sequence array
You used SWITCH , I used IFS, more ore less the same. I used very simple 2 dim sequence Only for the corners.
@@Excelambda yes, great suggestion. I always forget about IFS() too, that'd be nicer than switch. I'll have to get the laptop out again and check out your idea. Fantastic.
@@Excelambda I've had a quick test of your suggestion, and I understand it, but I'm not entirely sure how to apply it to the example in the video. Do you have your solution/formula? I'd be quite interested to see it in action.
I love you 😘 man
Glad you love the videos! I love you to, Teammate!!
Hi i found your Videoa very helpfull but i have a question can i subtract multiple cells from one cell for example B1:B10 from A1
Yes. That would be an array subtraction calculation : )
@@excelisfun kindly can you tell me the whole process how do i do it
@@kingandfana =A1-B1:B10
Hey how are you today sir? can u recomended a video where i can make searcheble drop down list.
i have 4000 items and idea was to make a search bar on top few cels and under is all my product that i selling. but i want when customer tipe few ford of items that he looking for under the search bar made a list of all items that contain that leather.. i hope you understand my bad english. chears
Very interesting, how do I create a power query for a table that contains numbers and text? The numbers contain an employee number that has a letter in it which cannot be removed and it's driving me nuts!
Can you convert it all to text?
You can't have mixed data in a column
@@excelisfun ok thank you, I will try that! I'm trying to streamline my data because I keep making mistakes with my data.
14:11 I do leave comments, alot of them :)
I can't get to all of them, but they HELP the Team Alot!!!! Thanks for helping the Team, Radoslaw : ) : ) : ) : )
@@excelisfun im trying :)
I also read upon how to get MVP :) and weirdly enough I do contribute on ExcelForum.pl website, I teach excel team I work with in my work, and now here i I find a way to help.
Maybe even I couldcontribute to your videos somehow :) (altho on't know what I could do :))
Help!!! Do you have a video with retention codes for a warehouse. Example code 94-1 will add 2 years to a box received in 2014 making the destruction date 2016. Also I have over 300 codes with same/different years for each.
I am not sure. try mrexcel.com/forum
Hey thanks!!!
I remember your old formula without XLOOKUP. Now it is easier with XLOOKUP, and it occurred to me to put "*" instead of "Total" and then summing up is very simple.
formula for list for data from 1528:
=IF(SEQUENCE(,COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0))))+1)>COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0)))),
"*",TRANSPOSE(SORT(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0))))))
And sum:
=SUMIFS(fRevenue5[Revenue],XLOOKUP(I4,fRevenue5[#Headers],fRevenue5),H10#,XLOOKUP(I5,fRevenue5[#Headers],fRevenue5),I9#)
P.S. I use conditional formatting to show "Total" instead of "*"
Very cool trick! Thanks, Teammate : )
Is anyone able to give me a hand with a similar topic, I cannot quite figure out the syntax.
Say for instance I have a table like this:
Plant | Material | Q1 Price | Q2 Price | Q3 Price | Q4 Price | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
I would like the result of my new array using Let to Look like this:
Plant | | Q1 | Q2 | Q3 | Q4 |
I was able to use the basic ideas in this video to write something like this
=LET(
List,UNIQUE(Forecast[Plant]),
REVCOL,Forecast[Jan],
REVCOL2,Forecast[Feb],
TEST,SUMIFS(REVCOL,Forecast[Plant],List),
CHOOSE({1,2},List,TEST))
This results in a table Plant | Jan which correctly summarizes the January volumes by plant -- Now it is time to make a more complex calculation for instance I would normally use:
=SUM(Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=$A1)) using a helper unique list to allow me to summarize what is essentially a sum product function, but I am trying to make that one of the calculations in my LET, such that my end result can be the following, allowing me to control a summary table layout:
=LET(
List,Unique(Forecast[Plant])
Q1,Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=List),
Q2,Filter((Forecast[Apr]+Forecast[May]+Forecast[Jun])*Forecast[Q2],Forecast[Plant]=List),
Q3,Filter((Forecast[Jul]+Forecast[Aug]+Forecast[Sep])*Forecast[Q3],Forecast[Plant]=List),
Q4,Filter((Forecast[Oct]+Forecast[Nov]+Forecast[Dec])*Forecast[Q4],Forecast[Plant]=List),
Choose({1,2,3,4,5,6},Plant,"",Q1,Q2,Q3,Q4))
Appreciate the help in advance here
How can we anoint you as as the Excel God? Just tell me. I'm all ears!
No Gods here ; ) Just a Team! I make and post videos and Excel files and you watch, learn and thumbs up and comment. Go Team!!!!
Dokywarun gela Mike, sorry...
=LET(MikeGirvin,"Great, Great, Great!",...
Thanks, jean : ) : )
It is too complicated for me. I don't seem to have a talent for excel :(
No, no, no..... !!!!! Of course you have talent for Excel : ) : ) This sort of formula is at the outer extreme. Few people do this sort of thing. Forget this sort of stuff and just have fun with Excel that way you have fun with Excel, Kasia : ) : ) : ) : )
Thank you so much for your encouraging reply. :) :)
Please can you speak hindi
I used this one :) =LET(a,SORT(UNIQUE(XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer))),b,TRANSPOSE(SORT(UNIQUE(XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer)))),c,SUMIFS(fSalesAnswer[Revenue],XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer),a,XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer),b),d,BYCOL(c,LAMBDA(q,SUM(q))),e,BYROW(c,LAMBDA(r,SUM(r))),VSTACK(HSTACK(K2&"/"&K3,b,"Total"),HSTACK(a,c,e),HSTACK("Total",d,SUM(d))))