Google sheets- How to process and dynamically update the data like in MS Access queries?

Question

community!
I have a table of 3 columns in Google Sheets: Date, Name, Amount.

---

• I want to process data from it, so for every date in the list, there will be a full list of participants and amounts.
• If there is no amount for specific date and person, it will be "0", or just leave it blank.
• In addition, would like to count the percent of the sum of the amount for each person till the date in the corresponding row.
• And it should be dynamically updated, so if a new row added with new name or date or both, so new participant will be added to every date in the output table and new full list of participants for the new date created.

---

For better understanding, here is the example of the data and output tables

---

What I've tried?
In MS access query it could be done not so hard.
Here I tried to create 2 new lists of unique dates and participants and connect them through use of CONCATENATE, ARRAYFORMULA, then SPLIT and some QUERY. All this through use of helping column of text, which should be edited manually for every new row...
The code in helping column:

=concatenate(arrayformula(if(isblank($F$3:$F),,";"&$E3&","&$F$3:$F)))  

Then split code:

=query(arrayformula(split(transpose(split(TEXTJOIN(";",true,$G$3:$G),";",true,true)),",",true,true)),"Select Col1,Col2 where Col2 is not null order by Col1 ASC",0)  

But here I stuck...
Want to pull the amounts for every corresponding date and participant, but...
FILTER reaches first, not existent in the original table combination, and doesn't proceed.
QUERY doesn't fetch the data fully automatically for all the list in the output table.
VLOOKUP gives only one row or complete mess, when use it recursively, or error...

---

So, how can I do this (if it's possible at all) in google sheets, and so all the output will be fully automatically updated?
Thank you very much!

Answer 1

Update - based on comments

I could not figure out a way to make this through one formula. If this works, you can have a hidden column (say H here) that totals the amount for the person till that date using the formula =QUERY(FILTER($E$3:$G, $F$3:$F = F3, $E$3:$E <= E3), "select sum(Col3) label sum(Col3) ''", 0) in H3.

Then in I3 you can find the % of total till date using the formula =QUERY(FILTER($E$3:$G, $F$3:$F = F3, $E$3:$E <= E3), "select sum(Col3) label sum(Col3) ''", 0)/SUMIF($E$3:$E, E3, $H$3:$H).

Both of the formulae in H3 and I3 need to be dragged down unlike the one in E3. Perhaps someone will be able to offer a better solution.

Previous answer

You can try something like this:

In E3 you can have the formula =ArrayFormula({(FLATTEN(SPLIT(REPT(FILTER(UNIQUE(A3:A)&"✦", UNIQUE(A3:A)>0), COUNTA(UNIQUE(B3:B))), "✦"))), (TRANSPOSE(SPLIT(REPT(JOIN("", FILTER(UNIQUE(B3:B)&"◼︎", UNIQUE(B3:B)<>"")), (COUNTA(UNIQUE(A3:A)))), "◼︎"))), (IFNA(VLOOKUP({(FLATTEN(SPLIT(REPT(FILTER(UNIQUE(A3:A)&"✦", UNIQUE(A3:A)>0), COUNTA(UNIQUE(B3:B))), "✦")))&(TRANSPOSE(SPLIT(REPT(JOIN("", FILTER(UNIQUE(B3:B)&"◼︎", UNIQUE(B3:B)<>"")), (COUNTA(UNIQUE(A3:A)))), "◼︎")))}, {A3:A&B3:B, C3:C}, 2, 0), 0))}). It is a little long but fills Columns E through G dynamically.

Then in H3 you can have the formula =ArrayFormula(IFERROR(((IFNA(VLOOKUP({(FLATTEN(SPLIT(REPT(FILTER(UNIQUE(A3:A)&"✦", UNIQUE(A3:A)>0), COUNTA(UNIQUE(B3:B))), "✦")))&(TRANSPOSE(SPLIT(REPT(JOIN("", FILTER(UNIQUE(B3:B)&"◼︎", UNIQUE(B3:B)<>"")), 3), "◼︎")))}, {A3:A&B3:B, C3:C}, 2, 0), 0))/(SUMIF((FLATTEN(SPLIT(REPT(FILTER(UNIQUE(A3:A)&"✦", UNIQUE(A3:A)>0), COUNTA(UNIQUE(B3:B))), "✦"))), (FLATTEN(SPLIT(REPT(FILTER(UNIQUE(A3:A)&"✦", UNIQUE(A3:A)>0), COUNTA(UNIQUE(B3:B))), "✦"))), G3:G))), "")). This one is dynamic as well.

I tried with your data and added a row on my own and it works.