• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data to change to Report

shili12

Member
I have this data in this form > see link of how i want my report to look like in full (file of 3.7 MB was too large to upload), i have been using combination of pivot tables, filter function, if its possible , i just copy paste rawdata and get results. Power query, macros, formulae , am on office 365 , any method is OK, i have GROUPBY and PIVOTBY but not regex as of yet .
Thanks in advance.
Transaction Date
Company NameCertificate TypeFirst Serial #Last Serial #Qty(Out/In)User Name
12/09/2024 16:41Acme insurance co. LtdType D - Motor CycleD5085369D5085468
100​
William Jones
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30326855C30330354
3,500​
William Jones
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314990C30314990
-1​
Acme API
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314991C30314991
-1​
Acme API
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314992C30314992
-1​
Acme API
12/09/2024 16:41Acme insurance co. LtdType B - Commercial VehicleB13866201B13867200
1,000​
William Jones
12/09/2024 16:41Acme insurance co. LtdType B - Commercial VehicleB13854297B13854297
-1​
Acme API
12/09/2024 16:41Acme insurance co. LtdType A - TaxiA12997444A12997543
100​
William Jones
12/09/2024 16:41Acme insurance co. LtdType A - TaxiA12994083A12994083
-1​
Acme API
12/09/2024 16:41Acme insurance co. LtdClass A - PSV UnmarkedA13127787A13128086
300​
William Jones

I want my report in this form >
Transaction DateCompany NameCertificate TypeFirst Serial #Last Serial #Qty(Out/In)User Name
12/09/2024 16:41Acme insurance co. LtdType D - Motor CycleD5085369D5085468
100​
William Jones
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30326855C30330354
3,500​
William Jones
12/09/2024 16:41Acme insurance co. LtdType B - Commercial VehicleB13866201B13867200
1,000​
William Jones
12/09/2024 16:41Acme insurance co. LtdType A - TaxiA12997444A12997543
100​
William Jones
12/09/2024 16:41Acme insurance co. LtdClass A - PSV UnmarkedA13127787A13128086
300​
William Jones
5000​
declarations(from agents)(errata here-its should be as agency/broker not insurance co. Ltd)
Transaction DateCompany NameCertificate TypeFirst Serial #Last Serial #Qty(Out/In)User Name
12/09/2024 17:12Skym insurance co. LtdType B - Commercial VehicleB13854311B13866236
-50​
declarations
12/09/2024 17:12VICT insurance co. LtdType B - Commercial VehicleB13866237B13866266
-30​
declarations
12/09/2024 17:13Coun insurance co. LtdType D - PSVD5106559D5106574
-16​
declarations
 
Last edited:

shili12

You should able have a sample data file. ( I skip all links to somewhere. )
Which shows as well as - what do You need?
I used Your post's-data.
Take care that Your real data's Qty are numbers.
Usage:
# Update Sheet1
# Select Sheet2 to see Your report ( It's almost as You've given with Your post ).
 

Attachments

  • shili12.xlsb
    19.7 KB · Views: 3
Thanks alot @vletm, however just one itty bitty error, ACMEAPI is 1 qty ,whereby declarations are greater than 1 . modify your code to distinguish declarations and ACMEAPI.. Below what read declarations should read ACMEAPI, and if they can be separated timewise, then ACMEAPI should come as summarised no of 1's. , like a pivot table. we dont want see lot of 1's. the file can now be uploaded as somehow you managed to compress it (in spite of my best efforts i couldnt)
Transaction DateCompany NameCertificate TypeFirst Serial #Last Serial #Qty(Out/In)User Name
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314990C30314990
-1​
declarations
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314991C30314991
-1​
declarations
12/09/2024 16:41Acme insurance co. LtdType C - Private CarC30314992C30314992
-1​
declarations
12/09/2024 16:41Acme insurance co. LtdType B - Commercial VehicleB13854297B13854297
-1​
declarations
12/09/2024 16:41Acme insurance co. LtdType A - TaxiA12994083A12994083
-1​
declarations
12/09/2024 16:42Acme insurance co. LtdType B - Commercial VehicleB13854298B13854298
-1​
declarations
12/09/2024 16:43Acme insurance co. LtdType D - Motor CycleD5084457D5084457
-1​
declarations
12/09/2024 16:43Acme insurance co. LtdClass A - PSV UnmarkedA13126842A13126842
-1​
declarations
12/09/2024 16:44Acme insurance co. LtdType C - Private CarC30314993C30314993
-1​
declarations
12/09/2024 16:45Acme insurance co. LtdType C - Private CarC30314994C30314994
-1​
declarations


should be in this format , all 1's are summarised and total presented
DateCertificate TypeSum of Qty(Out/In)
12/09/2024​
Acme insurance co. LtdClass A - PSV Unmarked
-9​
Acme insurance co. LtdType A - Taxi
-5​
Acme insurance co. LtdType B - Commercial Vehicle
-28​
Acme insurance co. LtdType C - Private Car
-138​
Acme insurance co. LtdType D - Motor Cycle
-8​
Acme insurance co. LtdType D - PSV
-2​
13/09/2024​
Acme insurance co. LtdClass A - PSV Unmarked
-72​
Acme insurance co. LtdType A - Taxi
-24​
Acme insurance co. LtdType B - Commercial Vehicle
-187​
Acme insurance co. LtdType C - Private Car
-820​
Acme insurance co. LtdType D - Motor Cycle
-35​
Acme insurance co. LtdType D - PSV
-31​
14/09/2024​
Acme insurance co. LtdClass A - PSV Unmarked
-36​
Acme insurance co. LtdType A - Taxi
-15​
Acme insurance co. LtdType B - Commercial Vehicle
-151​
Acme insurance co. LtdType C - Private Car
-554​
Acme insurance co. LtdType D - Motor Cycle
-17​
Acme insurance co. LtdType D - PSV
-14​
15/09/2024​
Acme insurance co. LtdClass A - PSV Unmarked
-3​
Acme insurance co. LtdType A - Taxi
-1​
Acme insurance co. LtdType B - Commercial Vehicle
-10​
Acme insurance co. LtdType C - Private Car
-47​
Acme insurance co. LtdType D - Motor Cycle
-1​
Acme insurance co. LtdType D - PSV
-1​
16/09/2024​
Acme insurance co. LtdClass A - PSV Unmarked
-2​
Acme insurance co. LtdType B - Commercial Vehicle
-5​
Acme insurance co. LtdType C - Private Car
-31​
Grand Total
-2247​
 

Attachments

  • shili12 (1).xlsb
    119.2 KB · Views: 1

shili12

Did You explain somewhere Your just one itty bitty error before my sample?
I guess that You're writing something about those -1 (for me -1 is different than 1).
What are Your named ACMEAPI?
Did You know that all Your given Transaction Dates ... something else than dates which has time too?
 

Attachments

  • shili12.xlsb
    77.3 KB · Views: 2
Thank you for your time and effort,i believe it will be used as organisational template, for staff to do tasks asap.
Also in 2020, you did a xlsm task for me , and it was used as a organisation template to lookup deposits and place those correctly to right bank account for over 5 months. Am much obliged.
ACMEAPI is motor certificates issued direct by insurance branches , whereas others are issued by agents.
Yes, i know about time issue, i have to be specific, after certificates get loaded, they start flowing out thru ACMEAPI(branches) and allotted to agents who are awaiting those. The time for branches is not so important, but for agents, it is, as they have made submissions in advance.
 
Last edited:
Back
Top