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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 19, 2024

NotSoFastEddie 

What do you mean under "the list of attributes" to be shown in each row - ATTRIBUTE_NAME, or ATTRIBUTE_VALUE or some combination of them?

And would you like to have each such value in separate cells, or all values could be as one text with some separator for the values?

Resources