Help with excel formula

[CarolG1860]

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

[#2]

@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. 

[#3]

Thank you Eric - I a very new to this!

[#4]

@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. 

[#5]

@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-ro...

f01 f02 f03 f04

Meg	Year 9	JDRAa Drama Booklet compulsory - (THC) - payment due 04.03.24	$10.00
Meg	Year 9	JMTHa Consumables (THC) - Term 1-2 - payment due 04.03.24	$90.00
Freddie	Year 9	JFNTa Food & Nutrition Consumables (THC)	$85.00
Freddie	Year 9	JAGRa Agriculture Consumables (THC) - payment due 29.07.24	$5.00
Freddie	Year 9	JMTHa Consumables (THC) - Term 1-2 - payment due 04.03.24	$90.00

Freddie	Year 9	JFNTa Food & Nutrition Consumables (THC)	$85.00	JAGRa Agriculture Consumables (THC) - payment due 29.07.24	$5.00	JMTHa Consumables (THC) - Term 1-2 - payment due 04.03.24	$90.00
Meg	Year 9	JDRAa Drama Booklet compulsory - (THC) - payment due 04.03.24	$10.00	JMTHa Consumables (THC) - Term 1-2 - payment due 04.03.24	$90.00