Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
Jul 19, 2024
Solved

Struggling with LAMBDA in my LET statement

I have a table that has repeating product numbers (P_NUM) and for each row, there is an attribute value.  I want to turn that into a single row with the P_NUM and the list of attributes.   The LET ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jul 20, 2024

    NotSoFastEddie 

    =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.

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
Jul 21, 2024
I have to ask DJ, what editor are you using? I don't see this type of syntax highlighting in my EXCEL.
djclements's avatar
djclements
Bronze Contributor
Jul 21, 2024

NotSoFastEddie The coloring has nothing to do with Excel. When posting on this website, you can use the </> button found on the toolbar to "Insert/Edit code sample", then select Excel Formula from the "Language" dropdown. To view the toolbar when posting a Reply, click the "Open full text editor" link found just below the initial "Enter reply text" frame.

Resources