Count unique days & add iteratively

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
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:
IDTypeStart DateEnd End
123Apple01/23/201907/14/2020
123Pear04/09/201905/21/2020
123Carrot07/24/201904/14/2021
789Bread01/01/202010/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:

Excel 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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,140,993
Messages
5,703,611
Members
421,305
Latest member
ambuj Thakur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top