I had posted a much simplified version of this under the "General" section of the forums here yesterday, however, my task has gotten way more complicated. Essentially, I was given tens of thousands of bills charged to customers. I am trying to determine if the bills are potentially accurate. The only data I have is the actual bill itself. Here's where things get complicated. There are four scenarios, each with their own nuances:
Scenario 1 Nuances:
1. Base Fee of $10 * the amount of items bought * the number of deliveries (maximum amount of items bought would be 10 and the maximum number of deliveries would be 7)
2. If the amount of items bought exceeds 1, the total bill is discounted by 20%
3. If the boxes are recycled, the total bill is further discounted by 15%
4. If the good is over 15 pounds, an additional charge of $10 will be assessed per each item over 15 pounds (maximum amount of items would be 10)
5. If there is no adequate port for delivery, an additional $50 per hour will be charged per delivery (maximum hours would be 3)
Scenario 2 Nuances:
1. Business that share a port of delivery for the second service will be charged $11 per item
2. If the business does not share a port of delivery, the fee is $13 per item. Each additionally delivery for a business that does not share a port is $15 per delivery, in addition to each item costing $13 (maximum deliveries would be 7 and maximum amount of items would be 8).
You see where I am going with all of this. The next two scenarios are just as lengthy. Sadly, no information besides the total bill is given. We don't know which scenario the bill belongs to, the number of items, etc., just a single column of the invoice. How would I go about determining all potential costs from each scenario to maybe index the invoice and see if there is a match? Any help on this would be amazing, whether it be in VBA or not.