Forum Discussion
Struggling with LAMBDA in my LET statement
=LET(
ATTRIBUTE_NAME,
VSTACK("P_SHAPE_CD","P_WEIGHT_G_AMT","P_WEIGHT_KG_AMT","P_WEIGHT_LB_AMT","THEME_MULTY_CD","TYPE_OF__CD","WHY_CD","_HEIGHT_CM_QTY","_HEIGHT_FT_QTY","_HEIGHT_IN_QTY","_LENGTH_CM_QTY","_HEIGHT_FT_QTY","_LENGTH_IN_QTY","_WEIGHT_KG_AMT","_WEIGHT_LB_AMT","_WIDTH_CM_QTY","_WIDTH_IN_QTY","_WIDTH_MM_QTY"),
TRANSPOSE(
HSTACK(
VSTACK("P_NUM",ATTRIBUTE_NAME),
DROP(
REDUCE("",SORT(UNIQUE(A2:A147)),LAMBDA(u,v,HSTACK(u,VSTACK(v,BYROW(ATTRIBUTE_NAME,LAMBDA(r,XLOOKUP(v&r,A2:A147&F2:F147,G2:G147,"")))))))
,,1))))
This formula returns the expected result in my sample file.
=IFNA(DROP(REDUCE("",UNIQUE(A2:A147),LAMBDA(u,v,VSTACK(u,HSTACK(v,TOROW(FILTER(F2:F147,A2:A147=v)))))),1),"")
Does this work? Otherwise could you share the expected result? Thank you.
Thanks for getting back to me. You are on the right track and it is certainly cleaner than what I have been working with.
Here are the expected results. After I initially submitted, I sorted the original data by PNUM and Attribute_Name which is why the order may look different to you.
=LET(
ATTRIBUTE_NAME,
VSTACK("P_SHAPE_CD","P_WEIGHT_G_AMT","P_WEIGHT_KG_AMT","P_WEIGHT_LB_AMT","THEME_MULTY_CD","TYPE_OF__CD","WHY_CD","_HEIGHT_CM_QTY","_HEIGHT_FT_QTY","_HEIGHT_IN_QTY","_LENGTH_CM_QTY","_HEIGHT_FT_QTY","_LENGTH_IN_QTY","_WEIGHT_KG_AMT","_WEIGHT_LB_AMT","_WIDTH_CM_QTY","_WIDTH_IN_QTY","_WIDTH_MM_QTY"),
TRANSPOSE(
HSTACK(
VSTACK("P_NUM",ATTRIBUTE_NAME),
DROP(
REDUCE("",SORT(UNIQUE(A2:A147)),LAMBDA(u,v,HSTACK(u,VSTACK(v,BYROW(ATTRIBUTE_NAME,LAMBDA(r,XLOOKUP(v&r,A2:A147&F2:F147,G2:G147,"")))))))
,,1))))
This formula returns the expected result in my sample file.
- Thank you very much Oliver - works beautifully!
