Automated Excel Data Formatting - Phase 2

This is the phase 2 of the project. We would like to read a specific call log and format the data with some additional calculations.

1. The application reads the values from the columns specified in the InboundRateCard.xlsx and loads into arrays
Company name
Organization ID
Billed DDI
Mobile Prefix
Landline Prefix
Mobile Charge Per Minute
Landline Charge Per Minute
Call Setup Fee Mobile - One off Per Call
Call Setup Fee Landline - One off Per Call
Round Up Minutes - Per Call

2. Calculation for a DDI (Mobile Source):
Firstly it needs to go through the ddi field in the raw data and match ddi value with the "Billed DDI" value.
Then It needs to determine what are the calls that came from mobile sources. So sort the "cli" value on the raw data matching "Mobile Prefix" value
Next step converting the duration of each call from the raw data to minute. If the value of "Round Up Minutes - Per Call" = Yes then the decimal should be rounded up to the full minute (e.g 1.34 minute should be rounded up to 2 minutes)

Now the calculation, For each call for the corresponding DDI:
Total Duration of the Call (Minutes) * value of "Mobile Charge Per Minute" + value of "Call Setup Fee Mobile - One off Per Call"

Now add the total cost value of all Calls from mobile sources for the DDI and keep in a variable.

3. Calculation for a DDI (Landline Source):
Firstly it needs to go through the ddi field in the raw data and match ddi value with the "Billed DDI" value.
Then It needs to determine what are the calls that came from Landline sources. So sort the "cli" value on the raw data matching "Landline Prefix" value (in our case landline is !447*)
Next step converting the duration of each call from the raw data to minute. If the value of "Round Up Minutes - Per Call" = Yes then the decimal should be rounded up to the full minute (e.g 1.34 minute should be rounded up to 2 minutes)

Now the calculation, For each call for the corresponding DDI:
Total Duration of the Call (Minutes) * value of "Landline Charge Per Minute" + value of "Call Setup Fee Landline - One off Per Call"

Now add the total cost value of all Calls from landline sources for the DDI and keep in a variable.

4. Exporting to a Report
From the Above calculation generate a new Excel sheet with the following info

Company name | Organization ID | Billed DDI | Inbound Charge - Mobile | Inbound Charge - Landline

*** Thing to consider, an organisation can have multiple Billed DDI so can appear in multiple rows in the generated report

5. Finally we would like to have a 3rd button on the control module that will combine the report for both the Inbound and Outbound call costs