Forum Discussion

MyQueries's avatar
MyQueries
Copper Contributor
Sep 27, 2024

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 dateSite IDCPOSales Order numberWBSQuantityUnit PriceInvoice valueATI number (Invoice output to customer)Line Item Text to include on invoiceBilling milestone CurrencyVATCustomerPayment terms
9/26/2024​
a
11​
  
0.7​
 
4201.22​
1​
A  AAAA
9/26/2024​
d
222​
  
0.5​
 
4201.22​
2​
A  AAAA
9/26/2024​
f
222​
  
0.1​
 
519​
2​
A  AAAA
9/26/2024​
b
11​
  
0.3​
 
679​
1​
A  AAAA
9/26/2024​
e
222​
  
0.1​
 
705​
2​
A  AAAA


Final Output needed

Invoice and Tax dateSite IDCPOSales Order numberWBSQuantityUnit PriceInvoice valueATI number (Invoice output to customer)Line Item Text to include on invoiceBilling milestone CurrencyVATCustomerPayment terms
9/26/2024​
a
11​
  
0.7​
 
4201.22​
1​
A  AAAA
9/26/2024​
b
11​
  
0.3​
 
679​
1​
A  AAAA
 Total     
4880.22​
1 Site 70% PO 11 ATI 1
1 Site 30% PO 11 ATI 1
A  AAAA
9/26/2024​
d
222​
  
0.5​
 
4201.22​
2​
A  AAAA
9/26/2024​
e
222​
  
0.1​
 
705​
2​
A  AAAA
9/26/2024​
f
222​
  
0.1​
 
519​
2​
A  AAAA
 Total     
5425.22​
1 Site 50% PO 222 ATI 2
2 Site 10% PO 222 ATI 2
A  AAAA


Please help me out how to perform this using VBA macro.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi MyQueries 

     

    With Get & Transform aka Power Query (Excel >/= 2016/Windows or 365/Mac):

     

    With attached file:

    • Put your data in the blue Table in sheet Input
    • Switch to sheet Output
    • Right-click in the green Table > Refresh
    • MyQueries's avatar
      MyQueries
      Copper Contributor
      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"

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        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

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor

    MyQueries 

    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​a11​  0.7​ 4201.22​1​A  AAAA
    9/26/2024​d222​  0.5​ 4201.22​2​A  AAAA
    9/26/2024​f222​  0.1​ 519​2​A  AAAA
    9/26/2024​b11​  0.3​ 679​1​A  AAAA
    9/26/2024​e222​  0.1​ 705​2​A  AAAA

    f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16  

    9/26/2024​a11​  0.7​ 4201.22​1​A  AAAA 
    9/26/2024​b11​  0.3​ 679​1​A  AAAA 
     Total     4880.221 site 30.0% ATI 1​,1 site 70.0% ATI 1​A  AAAA 
    9/26/2024​d222​  0.5​ 4201.22​2​A  AAAA 
    9/26/2024​f222​  0.1​ 519​2​A  AAAA 
    9/26/2024​e222​  0.1​ 705​2​A  AAAA 
     Total     5425.222 site 10.0% ATI 2​,1 site 50.0% ATI 2​A  AAAA