Sep 27 2024 05:55 AM
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.
Sep 27 2024 06:47 PM
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 |
Sep 27 2024 11:54 PM
Hi @Negi84
With Get & Transform aka Power Query (Excel >/= 2016/Windows or 365/Mac):
With attached file:
Sep 30 2024 01:00 PM
Sep 30 2024 01:35 PM
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
Sep 30 2024 10:53 PM
@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 ?
Sep 30 2024 11:48 PM
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 |
Oct 01 2024 12:01 AM
Can't access the file you shared 😞 Could you try again please?
Oct 01 2024 01:46 AM
Oct 01 2024 06:04 AM