How to Programmatically Split Data Using VBA Using Specific Logic
- by Charlene
This is an addition to my previous post here. The code that was previously supplied to me worked like a charm, but I am having issues modifying it adding some additional logic.
I am creating a macro in VBA to do the following. I have raw order data that I need to transform based on some logic.
Raw Data:
order-id product-num date buyer-name prod-name qty-purc sales-tax freight order-st
0000000000-00 10000000000000 5/29/2014 John Doe Product 0 1 1.00 1.50 GA
0000000000-00 10000000000001 5/29/2014 John Doe Product 1 2 1.00 1.50 GA
0000000000-00 10000000000002 5/29/2014 John Doe Product 2 1 1.00 2.00 GA
0000000000-01 10000000000002 5/30/2014 Jane Doe Product 2 1 0.00 0.00 PA
0000000000-01 10000000000003 5/30/2014 Jane Doe Product 3 1 0.00 0.00 PA
Desired Outcome:
HDR 0000000000-00 John Doe 5/29/2014
CHG Tax 3.00
CHG Freight 5.00
ITM 10000000000000 Product 0 1
ITM 10000000000001 Product 1 2
ITM 10000000000002 Product 2 1
HDR 0000000000-01 Jane Doe 5/30/2014
ITM 10000000000002 Product 2 1
ITM 10000000000003 Product 3 1
The "CHG" rows are created based on the following logic; if the order-st is CA or GA, add the total of sales-tax and freight for each of the rows with the same order-id. If the order-st is NOT CA or GA, no CHG rows should be created.
Any help would be appreciated - let me know if I left any details out!
