Forum Discussion
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 statement was provided by ChatGPT and should work, but I am having trouble with the LAMBDA functions. If I modify the data line to have getValues, instead of rowBuilder, I get the headings printed out and rows of data, with P_NUM having a #CALC error and the values have #N/A.
If I keep the formula as it is, I get a single #CALC error in the cell where the LET statement resides and nothing else.
I have added CLEAN and TRIM on P_NUM as I know I have had problems with type mismatch when extracting data from the original source - made no difference.
=LET(
PNums, UNIQUE(A2:A100),
attributeNames, UNIQUE(F2:F100),
attributeValues, G2:G100,
PNumRange, (A2:A100),
attributeNameRange, F2:F100,
headers, HSTACK("P_NUM", TRANSPOSE(attributeNames)),
getValues, LAMBDA(pn, LAMBDA(an, IFERROR(INDEX(attributeValues, MATCH(1, (PNumRange = pn) * (attributeNameRange = an), 0)), ""))),
rowBuilder, LAMBDA(pn, HSTACK(pn, MAP(attributeNames, getValues(pn)))),
data, BYROW(PNums, rowBuilder),
result, VSTACK(headers, data),
result
)
first result
second result when I change rowBuilder in the data line with getValues (to see what I get)
=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.
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?
NotSoFastEddie It looks like your formula is attempting to use MAP within BYROW to output an array of arrays, which is currently not supported in Excel.
One possible workaround is to generate one or more arrays of lookup_values with the same dimensions as the output array and use MAP to loop through them together.
Example 1: MAP with multiple lookup_value arrays
=LET( p_num, A2:A147, attrib_name, F2:F147, attrib_value, G2:G147, ARR, LAMBDA(n, CHOOSE(n, UNIQUE(p_num), TOROW(UNIQUE(attrib_name)))), results, MAP(ARR({1}), ARR({2}), LAMBDA(num,name, XLOOKUP(1, (p_num = num) * (attrib_name = name), attrib_value, ""))), HSTACK(VSTACK("P_NUM", ARR(1)), VSTACK(ARR(2), results)) )Example 2: MAP with a single, concatenated lookup_value array
=LET( p_num, A2:A147, attrib_name, F2:F147, attrib_value, G2:G147, lookup_array, p_num & "|" & attrib_name, nums, UNIQUE(p_num), names, TOROW(UNIQUE(attrib_name)), results, MAP(nums & "|" & names, LAMBDA(v, XLOOKUP(v, lookup_array, attrib_value, ""))), HSTACK(VSTACK("P_NUM", nums), VSTACK(names, results)) )However, in this particular example, MAP is not even needed:
=LET( p_num, A2:A147, attrib_name, F2:F147, attrib_value, G2:G147, lookup_array, p_num & "|" & attrib_name, nums, UNIQUE(p_num), names, TOROW(UNIQUE(attrib_name)), results, XLOOKUP(nums & "|" & names, lookup_array, attrib_value, ""), HSTACK(VSTACK("P_NUM", nums), VSTACK(names, results)) )See attached...
- Thanks again DJ! It looks very good.
- I have to ask DJ, what editor are you using? I don't see this type of syntax highlighting in my EXCEL.
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.
