Count unique days & add iteratively

[spydey]

I have been racking my brain, trying to figure this out but have been unsuccessful to this point so far.
I hope I can make this simple to follow, understand, and to the point.
I have a formula I currently use which works correctly, but it does a cumulative sum. I am hoping to adjust it to do an iterative sum, which I explain below.

I have a single workbook with 2 worksheets:

• Main Data
• Transactions

The Main Data worksheet has the following columns in it:

• UID
• Start Date
• End Date
• Adj. End Date

The UID only appears once in the Main Data, as would be assumed due to it being a UID.
The Start Date is always before or equal to the End Date.

The Transactions worksheet has the following columns in it:

• ID
• Type
• Start Date
• End Date

The ID can appear multiple times in the column.
The Start Date is always before or equal to the End Date.
For Example:

ID	Type	Start Date	End End
123	Apple	01/23/2019	07/14/2020
123	Pear	04/09/2019	05/21/2020
123	Carrot	07/24/2019	04/14/2021
789	Bread	01/01/2020	10/05/2020
...	...	...	...

The formula works as follows:

• It is input in the Adj. End Date in the Main Data worksheet via CSE.
• It sums all unique days (difference of End Date & Start Date) from the Transaction worksheet where the Main Data:UID matches the Transactions:ID, and the Transactions:Start Date is after or equal to the Main Data:Start Date and the Transactions:Start Date is before or equal to the Transactions:End Date.
  • It does not allow for duplicate days via overlap between the transactions.
• That sum is then added to the Main Data:End Date to get an Adj. End Date.

Here is the formula:

=SUM(--(MMULT((ROW(INDIRECT([@[Start Date]]&":"&[@[End Date]]))>=TRANSPOSE(IF(Transactions[ID]=[@UID],Transactions[Start Date])))*(ROW(INDIRECT([@[Start Date]]&":"&[@[End Date]]))<=TRANSPOSE(IF(Transactions[ID]=[@UID],Transactions[End Date]))),Transactions[Start Date]^0)>0))+[@End Date]

I am hoping to adjust the formula to be iterative. Not sure if I will need to add additional "helper" columns to make this work.

Basically, there are some situations where there might be several transactions for a single ID, but some of them start after the UID's End Date.
If we were to apply the number of days from those transactions that start prior to the UID's End date, we get an adjusted End date.
Then if we were to do it again, now using the "Adj. End Date" in place of the "End Date", it would adjust our "Adj. End Date" out even further".
So on and so forth until there are no more transactions that start before the most recently calculated Adj. End Date, or there are simply no more transactions for that UID.

Example:

• UID End Date is 06/05/2019.
• There are 3 transactions:
  • 1st starts 04/10/2019 for 90 days
  • 2nd starts 07/05/2019 for 50 days
  • 3rd starts 12/15/2019 for 100 days
• Currently, due to the 2nd & 3rd transactions not starting prior to the UID End Date, they would be ignored if using the formula above, thus giving us our Adj. End Date of 06/05/2019 +90 days = 09/03/2019
• However, if we did an iterative calculation, then each transaction would be evaluated individually against the UID Adj. End Date:
  • The first transaction would be evaluated against the UID End Date, found to meet the requirements, and added to the UID End Date giving us our Initial Adj. End Date of 09/03/2019
  • The 2nd transaction would then be evaluated against the UID Initial Adj. End Date of 09/03/2019, found to meet the requirements, and thus added to the Initial Adj. End Date of 09/03/2019, giving us our Updated Adj, End Date of 10/23/2019.
  • The 3rd transaction would then be evaluated against the Updated UID Adj. End Date of 10/23/2019, found to NOT meet the requirements and the evaluation would terminate.

The difficulty I see in doing this is:

• I never know how many transactions a single UID is going to have in the Transactions worksheet. It could be 1 or it could be hundreds.
• I would like to do it in a single formula, without any helper columns if possible (I doubt it is possible).
  • If helper columns are needed, would they be dependant on how many transactions a UID has?
• Ensuring only unique days are counted, avoiding overlap between one transaction's end date and another transaction's start date.

Any thoughts, suggestions, ideas .... therapists (I have been working on this for quite a while and find myself at wits end ? ), you all could assist with is greatly appreciated!

-Spydey