Forum Discussion
Solved
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 ...
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
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"
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
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 ?
https://1drv.ms/x/s!Ap80Ku6M2Tw5g1oVHtkj2oZUelJd?e=PZRyzJ