Forum Discussion
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.
- Hi Lorenzo,
Thanks a lot its work like a charm, I was not aware of we can achieve this by using Power Query.
I need minor change where we are filling ATI number (Invoice output to customer) is multiple in same CPO then in its description I need respective ATI number for example in below example we have 3 different ATI in that case description of summary should contains final output as below :-
Invoice and Tax date Site ID CPO Sales Order number WBS Quantity Unit Price Invoice value ATI number (Invoice output to customer)
8/27/2028 56311 111 581986985 ETL14.100005/04105 0.7 6,048.23 4,233.76 1
8/27/2029 12614 111 581986985 ETL14.100005/04104 0.3 6,048.23 4,233.76 2
8/27/2031 25543 111 581986985 ETL14.100005/04115 0.3 6,048.23 4,233.76 3
Total 12,701.28 "1 Site 70% PO 111 ATI 1
1 Sites 30% PO 111 ATI 2
1 Sites 30% PO 111 ATI 3"Hi MyQueries
I need minor change... It's probably not a minor change. The info./example you shared leads to different possible interpretations/results
==> Share (with OneDrive, Google Drive... or the like) a sample representative workbook with a couple of examples showing what you have & what you expect - each example being different/representing your different possible scenarios
Hi,
As to data ETL (Extract transform and loading),another option is sql like:
select * from GenerateSubtotal limit 20;
create temp table aa as
select f03||'t' ord,'' f01,'Total' f02,f03,
colIdxf[3:6]{'' %s}, sum(f08) f08,
f09,f10,f11,f12,f13,f14,f15,f16 from GenerateSubtotal group by f03 order by ord;
create temp table bb as
select f03,group_concat(ATI) f09 from (select f03,f06,count(f06)||' site '||cast(f06*100 as text)||'% ATI '||f09 ATI from GenerateSubtotal group by f03,f06) group by f03;
create temp table cc as
select ord,f01,f02,'' f03,f04,f05,f06,f07,f08,bb.f09,f10,f11,f12,f13,f14,f15,f16 from aa join bb using(f03);
//select * from cc;
create temp table dd as
select f03 ord,* from GenerateSubtotal union all select * from cc order by ord;
select colIndex[1:] from dd;
f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16
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 f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16
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 30.0% ATI 1,1 site 70.0% 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 f 222 0.1 519 2 A A A A A 9/26/2024 e 222 0.1 705 2 A A A A A Total 5425.22 2 site 10.0% ATI 2,1 site 50.0% ATI 2 A A A A A
