Creating headers (and footers) in the middle of returned query data with Google Sheets

Question

I have a table of data that I want to turn from raw data into something a little 'prettier' and organized to look at. The data consists of payments made (3 columns: Name, payment amount, and date of payment).

I'd like to present the data so that for each group of dates that are the same there is a header above those rows with the date itself so the user can see these rows of dates are for say 1/14/2023.

Then the next set of rows for say 1/15/2023 would have the header 1/15/2023.

Please see the attached image.screenshot

In columns A - C is the raw data and I don't know how many and which dates there will be a priori. In columns E-G is the desired output.

You can see I'd also like if possible a footer that sums the totals for that day.

Any ideas on how to achieve this? The ideal solution would be such that you could paste raw data into columns A-C and then the data shows up in columns E-G formatted. I'm hoping there's a design pattern for this type of thing before I cobble together a solution.

I've implemented something through Apps Script, but am more interested in learning if there's a way through the formulas provided in Sheets.

I can see this possibly being done with temp/work tables, but am wondering if this is a common problem/solution and would love to get pointed in the right direction. I'm a programmer and have been using sheets for about a year and can write queries and filters and do lots of things but want to learn the right way and not always just do something the first way that pops into my head that works.

Answer 1

try this formula along with a bit of conditional formatting to get the expected output.

Headsup: Set Columns E, F to DATE Type formatting

=LAMBDA(czx,MAP(INDEX(czx,,1),INDEX(czx,,2),INDEX(czx,,3),LAMBDA(ax,bx,cx,{ax,IF(ISNUMBER(ax),{"",""},IF(ax="TOTALS",{"",cx},IF(LEN(ax),{bx,cx})))})))(SORT(LAMBDA(ax,FILTER(ax,INDEX(ax,,1)<>""))({BYROW(UNIQUE(B:B),LAMBDA(bx,IF(bx="",,{bx,bx,0})));A:C;BYROW(UNIQUE(B:B),LAMBDA(bx,IF(bx="",,{"TOTALS",bx,SUMIF(B:B,bx,C:C)})))}),2,1))