Data Transformation using VBA like subtotal

Copper Contributor

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.

9 Replies

@Negi84 

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 

Screenshot_2024-09-28-09-41-04-170_com.mmbox.xbrowser.pro.jpg

Hi @Negi84 

 

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

Sample.png

 

With attached file:

  • Put your data in the blue Table in sheet Input
  • Switch to sheet Output
  • Right-click in the green Table > Refresh
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 @Negi84 

 

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

@Lorenzo sorry for that, I also noticed later when checked in whole dataset.

 

I have updated the file in below given link. let me know if you are able to access it ?

 

PQ_Negi84 ver1.xlsx

@Negi84 

sql:

select * from GenerateSubtotal limit 20;

create temp table aa as
select 'a'||f03||'t' ord,'' f01,'Total' f02,f03,
colIdxf[3:6]{'' %s}, sum(cast(replace(f08,',','') as double)) 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,'<br>') f09 from (select f03,f06,count(f06)||' site '||cast(f06*100 as text)||'% ATI '||f09 ATI from GenerateSubtotal group by f03,f06,f09) group by f03;

// change group from f03,f06 to f03,f06,f09
//select * from bb;
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 'a'||f03 ord,* from GenerateSubtotal union all select * from cc order by ord;
select colIndex[1:] from dd;

 

f01f02f03f04f05f06f07f08f09f10f11f12f13f14f15f16
27.08.202414140111WBS10.76,048.234,233.761DummyTestingTestingTestingTestingTestingTesting
27.08.202566097222WBS20.76,048.234,233.762DummyTestingTestingTestingTestingTestingTesting
27.08.202634541333WBS10.76,048.234,233.763DummyTestingTestingTestingTestingTestingTesting
27.08.202798604111WBS20.36,048.234,233.764DummyTestingTestingTestingTestingTestingTesting
27.08.202856311444WBS10.76,048.234,233.765DummyTestingTestingTestingTestingTestingTesting
27.08.202912614444WBS20.36,048.234,233.762DummyTestingTestingTestingTestingTestingTesting
27.08.203096919555WBS10.76,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
27.08.203125543444WBS20.36,048.234,233.767DummyTestingTestingTestingTestingTestingTesting
27.08.203258155222WBS20.36,048.234,233.768DummyTestingTestingTestingTestingTestingTesting
27.08.203096919555WBS10.76,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
27.08.203096919555WBS10.36,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
27.08.203125543444WBS20.36,048.234,233.767DummyTestingTestingTestingTestingTestingTesting
f01f02f03f04f05f06f07f08f09f10f11f12f13f14f15f16
27.08.202414140111WBS10.76,048.234,233.761DummyTestingTestingTestingTestingTestingTesting
27.08.202798604111WBS20.36,048.234,233.764DummyTestingTestingTestingTestingTestingTesting
 Total     8467.521 site 30.0% ATI 4DummyTestingTestingTestingTestingTestingTesting
        1 site 70.0% ATI 1       
27.08.202566097222WBS20.76,048.234,233.762DummyTestingTestingTestingTestingTestingTesting
27.08.203258155222WBS20.36,048.234,233.768DummyTestingTestingTestingTestingTestingTesting
 Total     8467.521 site 30.0% ATI 8DummyTestingTestingTestingTestingTestingTesting
        1 site 70.0% ATI 2       
27.08.202634541333WBS10.76,048.234,233.763DummyTestingTestingTestingTestingTestingTesting
 Total     4233.761 site 70.0% ATI 3DummyTestingTestingTestingTestingTestingTesting
27.08.202856311444WBS10.76,048.234,233.765DummyTestingTestingTestingTestingTestingTesting
27.08.202912614444WBS20.36,048.234,233.762DummyTestingTestingTestingTestingTestingTesting
27.08.203125543444WBS20.36,048.234,233.767DummyTestingTestingTestingTestingTestingTesting
27.08.203125543444WBS20.36,048.234,233.767DummyTestingTestingTestingTestingTestingTesting
 Total     16935.041 site 30.0% ATI 2DummyTestingTestingTestingTestingTestingTesting
        2 site 30.0% ATI 7       
        1 site 70.0% ATI 5       
27.08.203096919555WBS10.76,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
27.08.203096919555WBS10.76,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
27.08.203096919555WBS10.36,048.234,233.766DummyTestingTestingTestingTestingTestingTesting
 Total     12701.281 site 30.0% ATI 6DummyTestingTestingTestingTestingTestingTesting
        2 site 70.0% ATI 6       

@Negi84 

Can't access the file you shared 😞 Could you try again please?

@Negi84 

(Thanks for re-sharing)

Could you check the attached updated query does what you expect?