@gvitullib
The solution is just amazing! I often struggle with Lambda, but this video also helps me to understand better the purpose of the function. Great fan of Let (reminds me of the variables they use in Dax). Thanks Mike!
@ExcelUpNorth
I have no words. This was an incredible video! I didn’t think this was possible and accepted PowerQuery as the primary way to unpivot.
@GeertDelmulle
I nominate this Unpivot custom function for the FOTY-award (formula of the year). Fantastic, Excelambda, you knocked it out of the park with this one!
@johnborg5419
Unbelievable!!! Thanks Mike and to all that contributed in here!!
@ricos1497
Fantastic. I made an unpivot function when LET first came out alongside sequence and it was way more complicated than this! It's great that the subsequently released functions can all be utilised to make things much easier. The only thing I'd suggest is that you add a check that the number of row field names matches the number of row criteria columns (and same for columns), and that if an array of rowfieldnames is passed, that it is a horizontal array, so that it HSTACKs nicely as a header!
@IvanCortinas_ES
Strong Mike!!! A very intensive learning tutorial, a trademark of the house. Thank you for sharing it.
@wrapthreesome3398
Absolute insanity! This saves me so much time. Thank you guys.
@barttitulaerexcelbart9400
Thanks Mike, wonderful video. I will also add this formula to my book.xlt file. Next to your amazing AllFormula's function (where you created an overview of all functions and addresses in a range.
@davidabuang
Friendly tip: add an optional ScanByCol argument to the function definition so the end user can easily change the output order from row-major to column-major order. You don’t even need to handle it with IF-ISOMITTED… just plug it into the scan by column argument for each of the 3 instances of TOCOL used, and the default will be treated as FALSE if omitted. Then set it to 1 or TRUE to see the difference.
@nadermounir8228
Super amazing video Mike 🎉
@muhammadtambawala6379
Thanks, Mike! I loved this video. My formula below was inspired by one of your earlier videos. =LET( _data,B6:F26, _rows,ROWS(_data)-1, _cols,COLUMNS(_data)-1, _city,TOCOL(IF(SEQUENCE(_rows,_cols),DROP(TAKE(_data,,1),1))), _cellCompany,TOCOL(IF(SEQUENCE(_rows,_cols),DROP(TAKE(_data,1),,1))), _values,TOCOL(DROP(_data,1,1)), _fullTable,HSTACK(_city,_cellCompany,_values), _result,FILTER(_fullTable,CHOOSECOLS(_fullTable,3)<>0), _result)
@pauldfm1970
A colleague of mine used offset years ago to achieve the same. Then powerquery….now Lamda…..when standard function? This video was superb!
@chrism9037
Nice job Mike!! GO Team!!!!
@elbadlis
Thank you sharing the trick, Mike! Here is alternative formula based on sheet 1887() : =LET( data, C7:F26, F, LAMBDA( x, TOCOL( IF( data<>"",x,NA()),3 ) ), HSTACK( F(B7:B26), F(C6:F6), F(data) ) )
@jazzista1967
Mike... I just had to deal with a handful of reports at work yesterday that i needed to Unpivot and I decided to give excel a try instead of powerquery. Here is a less verbose version of the formula to unpivot and taking advantage of the new Dynamic arrays behavior called broadcast which basically resizes all the arrays to be in the same shape.
My Variables X= Carrier Y=City Z= Rating
VSTACK({"City","Carrier","Rating"},
LET(x,C6:F6,
y,B7:B26,
z,C7:F26,
result,
HSTACK(TOCOL(IF(z<>0,y,NA()),3),
TOCOL(IF(z<>0,x,NA()),3),
TOCOL(IF(z<>0,z,NA()),3)),
result))
@komanguy
Wow! Awesome! So many tips in this great video. Can't believe it! Man! Thank you very much.
@iankr
This is fantastic! Many thanks, Mike. 🙂
@Excel-end
Thanks for the Excellent custom function for unpivot, Here is the alternative, =LET(r,C7:F26,l,LAMBDA(x,TOCOL(IFS(r,x),3)),HSTACK(l(B7:B26),l(C6:F6),l(r)))
@ennykraft
Very nice! My solution is longer cause I used my go to formula for repeating values a certain number of times: =LET( r,B7:B26, c,C6:F6, v,C7:F26, a,SORT(CHOOSEROWS(r,MOD(SEQUENCE(COUNTA(r)*COUNTA(c),,0),COUNTA(r))+1)), b,TRANSPOSE(CHOOSECOLS(c,MOD(SEQUENCE(COUNTA(c)*COUNTA(r),,0),COUNTA(c))+1)), n,TOCOL(v), array,HSTACK(a,b,n), array) I chose not to eliminate empty values cause IMO that information is important too.
@excelisfun