How to use formulas to combine data on customer payments into one line of the financial calendar to automate it?

Question

Google Sheets

You need to automate your financial calendar.
https://docs.google.com/spreadsheets/d/1EAEEU-fDc8...
Here is a table and an example of how it should turn out.

Given a table with the guest, the amount of payment per day and the number of paid days.

It is necessary to automatically distribute the tariff for the number of days in the calendar table.
The difficulty is that from the guest (guest1) paid 2 times for different dates (the dates do not overlap) and it was taken out as a separate line. It is necessary that the paid rates of this guest are combined into one line of the financial calendar and distributed by paid days.

I could only do so that only one of the tariffs is distributed, but it is necessary that both are added depending on the payment date and the end date.

Answer 1

Maybe,

=INDEX(IF(
  VLOOKUP(C6:AG6;{D3:D4\F3:F4};2)>=C6:AG6;
  VLOOKUP(C6:AG6;{D3:D4\C3:C4};2);
))

Please note that for a larger number of unique guests, it will be necessary to complicate the index and selection criteria.
https://docs.google.com/spreadsheets/d/1tU2tNlqb1r...