Data Transformation using VBA like subtotal
Hi All,
I have excel file where I have approx. 38 columns but in order to create report I need only below mentioned headers from that report (approx 15 columns ).
Logic :-
1) First remove all extra columns apart from below mentioned headers
2) Sort CPO column in ascending order and insert a new row below every new CPO number.
3) Now I want to type "Total " Below Site ID
4) then it should show total of invoice value below Invoice value column
5) Under ATI column it should show data in format like : (count of unique quantity) Site +quantity in %+ CPO +ATI
eg : we have CPO number 222 3 times and unique quantity under it are 0.5 & 0.1 , CPO number 222, ATI is 2 then output for this should be like below
1 Site 50% PO 222 ATI 2
2 Site 10% PO 222 ATI 2
6) Type whatever values mentioned in column for respective CPO for column :Line Item Text to include on invoice, "Billing milestone" , "Currency " , "VAT", "Customer ", Payment terms " in new row inserted as per point 2
Below is my raw data :-
Invoice and Tax date | Site ID | CPO | Sales Order number | WBS | Quantity | Unit Price | Invoice value | ATI number (Invoice output to customer) | Line Item Text to include on invoice | Billing milestone | Currency | VAT | Customer | Payment terms | |
9/26/2024 | a | 11 | 0.7 | 4201.22 | 1 | A | A | A | A | A | |||||
9/26/2024 | d | 222 | 0.5 | 4201.22 | 2 | A | A | A | A | A | |||||
9/26/2024 | f | 222 | 0.1 | 519 | 2 | A | A | A | A | A | |||||
9/26/2024 | b | 11 | 0.3 | 679 | 1 | A | A | A | A | A | |||||
9/26/2024 | e | 222 | 0.1 | 705 | 2 | A | A | A | A | A |
Final Output needed
Invoice and Tax date | Site ID | CPO | Sales Order number | WBS | Quantity | Unit Price | Invoice value | ATI number (Invoice output to customer) | Line Item Text to include on invoice | Billing milestone | Currency | VAT | Customer | Payment terms | |
9/26/2024 | a | 11 | 0.7 | 4201.22 | 1 | A | A | A | A | A | |||||
9/26/2024 | b | 11 | 0.3 | 679 | 1 | A | A | A | A | A | |||||
Total | 4880.22 | 1 Site 70% PO 11 ATI 1 1 Site 30% PO 11 ATI 1 | A | A | A | A | A | ||||||||
9/26/2024 | d | 222 | 0.5 | 4201.22 | 2 | A | A | A | A | A | |||||
9/26/2024 | e | 222 | 0.1 | 705 | 2 | A | A | A | A | A | |||||
9/26/2024 | f | 222 | 0.1 | 519 | 2 | A | A | A | A | A | |||||
Total | 5425.22 | 1 Site 50% PO 222 ATI 2 2 Site 10% PO 222 ATI 2 | A | A | A | A | A |
Please help me out how to perform this using VBA macro.