Help with excel formula

Copper Contributor

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 understand what it is that I am wanting to do.

You will see on the "original" tab the way that the data is exported from our SMS and on the "end result: tab is how we need to data to show to insert in the correct letter fields in our SMS.

I am guessing it will be a IF/THEN formula?

So pretty much we need the row data to be converted to the columns Fields 1-6

4 Replies

Re: Help with excel formula

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.

Re: Help with excel formula

Thank you Eric - I a very new to this!

Re: Help with excel formula

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.

Re: Help with excel formula

Hi ,

sql:

select * from basic_group_concat limit 20;