Forum Discussion

CarolG1860's avatar
CarolG1860
Copper Contributor
Mar 18, 2024

Help with excel formula

I am a self taught novice of excel and formulas and I am hoping that someone in this forum who is a formula guru can help guide me through a process. I will attach a sample document so that you can ...
community
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 19, 2024

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. 

Resources