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)
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) Read More