Matching contract information to monthly income data
I am trying to build a monthly bus leasing income report in excel. Part of my problem is that there is an overlap with some of the data, that being vehicle registration. Also my accounts data that I have changes how data related to a contract is stored. Sometimes it will use the vehicle reg as the identifier which means a contract start date/end date is useful in seeing which contract the monthly income data is tied to. Or it uses a contract ID or rental ID. These are unique in telling contracts apart as every contract will have different contract ID's and Rental ID's. One of the issues I have found is that in the accounts data a contract may start by using reg as a unique identifier but then changes to contract ID in the 2nd month of data and then by the end of the contract switches to the Rental ID. The reason for the change to rental ID can be explained due to a change in the vehicle data system which switched to using the rental ID over contract ID. However I need to come up with a way to extract all the correct monthly income data for each contract. I have around 1200 rows worth of contracts. What i think I need to do is figure out a way to pull the contract ID and rental ID if there is any for each specific contract. It wont matter about a start date and end date when pulling this data as any data relating to the Contract ID and Rental ID is relevant to the contract. However I also need to try and pull data within the start date and end date of the contract relating to the registration of the vehicle as some of the income data may have been recorded only using the reg instead of a contract/rental ID. If you can help me figure out how to best do this or if there is a better complete different way to do this please let me know. I have attached images below of the sort of data i am working
I have customer contract information such as:
- Customer name
- Vehicle Registration
- Rental ID
- Contract ID
- Start Date
- End Date
This data has been imported from a live excel doc using power query where it contains two sheets that I imported. One of them being Current contract data and one being terminated contract data. Inside of power query I have cleaned the data the best I can and extracted the most important columns of data which is what you see above. The terminated contract data had problems with start dates and end dates and I had to merge it with a document called Report1 which had a lot of the correct start dates and end dates for the terminated contracts. Below is an idea of what my data looks like where there may be multiple contracts with the same customer. There may also be multiple uses of the same registration whether that be with the same customer or different customers. Rental and Contract ID are not fully populated, this is because there was a change to a newer system which uses rental ID now. However Rental ID and contract ID are unique to specific contracts and will never share any overlaps with other contracts. Ultimately I need to find a way to match this data to the monthly income data.
Image 1: All Contracts Snippet
The next part of my data is where the monthly income data is located. This shows information such as:
-Date
-Description
-Vehicle Registration
- Net (INCOME FIGURE I AM TRYING TO USE TO POPULATE MY REPORT)
Again I have cut back on what is visible in this table as these are the parts of main relevance.
Image 2: Accounts Data Example
So as you can see in image 2 this is a sample of what my monthly income data looks like where it shows the data which is usually the last day of the month, a description that I need to find the best way to extract the relevant information, the vehicle registration and the Net figure which is what I want to use to populate my report in Image 3 where each month in a specific contract has its income data populated. However the challenge is finding a way to pull just the contract number or reg as the data. Sometimes there is text after the contract ID.
Here is what I am trying to populate where I pull the customer data from All Contracts (IMAGE 1) and populate that data here. Then I want to try and match the data from All contracts most likely using rental ID, Contract ID AND the reg data BUT only between the Start date and end date for that specific contract. Then it would hopefully fill in the income data for that month in that year for the specific contract and if there is no income data in a specific month it would return 0.
Thanks for reading. Please let me know if you can help me solve this, i greatly appreciate it