VBA - I need help conceptualizing how to do something

jardenp

Active Member
Joined
May 12, 2009
Messages
367
I'm not sure how to proceed conceptually here and would appreciate the input of the masters. You all are my go-to experts!

I have two sheets:

Sheet "Transactions"
DateInvoiceEquipment NumberMeterCostAccount
1/5/20212051001000$250Oak
1/8/20212101001500$1000Fir
1/12/20212152003000$500Tulip
1/15/20212202003500$1200Oak

Sheet "Use"
DateEquipment NumberBeginning MeterEnding MeterWidget Units UsedAccount
1/6/202110010001300350Fir
1/7/202110013001500220Beech
1/13/202120030003200250Tulip
1/14/202120032003500325Pine

Basically, I have two sets of data that sort of line up but not quite fully. I need to reassign costs of transactions according to the widget units used by an account between the unit's transaction and the unit's previous transactions. The data I have to line up transactions and use are meter readings.

For equipment number 100, it had a $1000 transaction on 1/8/21 that covered 500 meter units since its last transaction. 570 widget units were used between that transaction and the last. So that's $1000 for 570 widget units or $1000/570 per widget unit. Therefore, account Fir should be charged $614.04 (350 widget units @ $1000/570 per widget unit) and account Beech should be charged $385.96 (220 widget units @ $1000/570 per widget unit). Likewise, for equipment number 200. It had a $1200 transaction on 1/15/21 that covered 575 widgets that should result in account Tulip being charged $521.74 (250 widget units @ $1200/575 per widget unit) and Pine being charged $678.26 (325 widget units @ $1200/575 per widget unit).

The end result would be listing of the original invoice and a breakdown of costs per account (maximum of 6 accounts). The two lines from the example transactions would be:
InvoiceTotal CostOriginal AccountReassignedAccount1ReassignedCost1ReassignedAccount2ReassignedCost2...ReassignedAccount6ReassignedCost6
210$1000FirFir$614.04Beech$385.96
220$1200OakTulip$521.74Pine$678.26

To complicate things, the meter readings in the use data may stradle a couple transactions. For example, an account may use 500 widget units, 300 of which may be part of one transaction and the other 200 part of the following transaction.

I'm not asking for a finished piece of code. I just can't figure out how to match up the two data sets. So far I have that each transaction needs to have a cost per widget unit calculated and then applied to the number of widget units. But I'm not sure which VBA device/technique to use to figure out which transaction(s) apply to a use data line.

Any help would be greatly appreciated!

Thanks!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,071
Members
416,010
Latest member
NJT

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