Help with excel formula

CarolG1860 

Sadly, you will not like the dynamic array solution to your problem.  What should be straightforward, results in a #CALC! error (nested arrays are not supported).  Given that the majority of worthwhile calculations have nested arrays as the intended output, this represents a massive error on behalf of Microsoft in specifying what is otherwise brilliant functionality!

= LET(
    distinctName, UNIQUE(name),
    recordϑ, MAP(distinctName, LAMBDA(d, LAMBDA(TOROW(FILTER(fields, name=d))))),
    record,  MAKEARRAY(COUNTA(distinctName),6, LAMBDA(r,c, INDEX(INDEX(recordϑ, r, 1)(),c))),
    result,  HSTACK(distinctName, XLOOKUP(distinctName, name, level), record),
    IFERROR(result, "")
  )

I have also included a simpler formula that may be filled down.  It might be that Power Query offers a better solution to your problem by pivoting the table without aggregation. 

CarolG1860 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

CarolG1860 

Hi ,

sql:

select * from basic_group_concat limit 20;

cli_no_header;

select f01,f02,group_concat(f03||'</td><td>'||f04,'</td><td>') from basic_group_concat group by f01,f02

https://answers.microsoft.com/en-us/msoffice/forum/all/combining-multiple-columns-and-rows-into-1-row/069cdc30-0a34-469f-8260-c053e20cfc77

f01 f02 f03 f04