Data Transformation using VBA like subtotal

[Negi84]

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.

[#2]

@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​

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

 

[#3]

Hi @Negi84 

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

[#4]

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"

[#5]

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

[#6]

@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

[#7]

@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;

f01	f02	f03	f04	f05	f06	f07	f08	f09	f10	f11	f12	f13	f14	f15	f16
27.08.2024	14140	1	11	WBS1	0.7	6,048.23	4,233.76	1	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2025	66097	2	22	WBS2	0.7	6,048.23	4,233.76	2	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2026	34541	3	33	WBS1	0.7	6,048.23	4,233.76	3	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2027	98604	1	11	WBS2	0.3	6,048.23	4,233.76	4	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2028	56311	4	44	WBS1	0.7	6,048.23	4,233.76	5	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2029	12614	4	44	WBS2	0.3	6,048.23	4,233.76	2	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2030	96919	5	55	WBS1	0.7	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2031	25543	4	44	WBS2	0.3	6,048.23	4,233.76	7	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2032	58155	2	22	WBS2	0.3	6,048.23	4,233.76	8	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2030	96919	5	55	WBS1	0.7	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2030	96919	5	55	WBS1	0.3	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2031	25543	4	44	WBS2	0.3	6,048.23	4,233.76	7	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
f01	f02	f03	f04	f05	f06	f07	f08	f09	f10	f11	f12	f13	f14	f15	f16
27.08.2024	14140	1	11	WBS1	0.7	6,048.23	4,233.76	1	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2027	98604	1	11	WBS2	0.3	6,048.23	4,233.76	4	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
	Total						8467.52	1 site 30.0% ATI 4	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
								1 site 70.0% ATI 1
27.08.2025	66097	2	22	WBS2	0.7	6,048.23	4,233.76	2	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2032	58155	2	22	WBS2	0.3	6,048.23	4,233.76	8	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
	Total						8467.52	1 site 30.0% ATI 8	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
								1 site 70.0% ATI 2
27.08.2026	34541	3	33	WBS1	0.7	6,048.23	4,233.76	3	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
	Total						4233.76	1 site 70.0% ATI 3	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2028	56311	4	44	WBS1	0.7	6,048.23	4,233.76	5	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2029	12614	4	44	WBS2	0.3	6,048.23	4,233.76	2	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2031	25543	4	44	WBS2	0.3	6,048.23	4,233.76	7	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2031	25543	4	44	WBS2	0.3	6,048.23	4,233.76	7	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
	Total						16935.04	1 site 30.0% ATI 2	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
								2 site 30.0% ATI 7
								1 site 70.0% ATI 5
27.08.2030	96919	5	55	WBS1	0.7	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2030	96919	5	55	WBS1	0.7	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
27.08.2030	96919	5	55	WBS1	0.3	6,048.23	4,233.76	6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
	Total						12701.28	1 site 30.0% ATI 6	Dummy	Testing	Testing	Testing	Testing	Testing	Testing
								2 site 70.0% ATI 6

[#8]

@Negi84 

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

[#9]

Please try now

https://1drv.ms/x/s!Ap80Ku6M2Tw5g1lEf4e3NgpHZJBl?e=aIAEpM

[#10]

@Negi84 

(Thanks for re-sharing)

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