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.
I want my report in this form >
Thanks in advance.
Transaction Date | Company Name | Certificate Type | First Serial # | Last Serial # | Qty(Out/In) | User Name |
---|---|---|---|---|---|---|
12/09/2024 16:41 | Acme insurance co. Ltd | Type D - Motor Cycle | D5085369 | D5085468 | 100 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type C - Private Car | C30326855 | C30330354 | 3,500 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type C - Private Car | C30314990 | C30314990 | -1 | Acme API |
12/09/2024 16:41 | Acme insurance co. Ltd | Type C - Private Car | C30314991 | C30314991 | -1 | Acme API |
12/09/2024 16:41 | Acme insurance co. Ltd | Type C - Private Car | C30314992 | C30314992 | -1 | Acme API |
12/09/2024 16:41 | Acme insurance co. Ltd | Type B - Commercial Vehicle | B13866201 | B13867200 | 1,000 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type B - Commercial Vehicle | B13854297 | B13854297 | -1 | Acme API |
12/09/2024 16:41 | Acme insurance co. Ltd | Type A - Taxi | A12997444 | A12997543 | 100 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type A - Taxi | A12994083 | A12994083 | -1 | Acme API |
12/09/2024 16:41 | Acme insurance co. Ltd | Class A - PSV Unmarked | A13127787 | A13128086 | 300 | William Jones |
I want my report in this form >
Transaction Date | Company Name | Certificate Type | First Serial # | Last Serial # | Qty(Out/In) | User Name |
12/09/2024 16:41 | Acme insurance co. Ltd | Type D - Motor Cycle | D5085369 | D5085468 | 100 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type C - Private Car | C30326855 | C30330354 | 3,500 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type B - Commercial Vehicle | B13866201 | B13867200 | 1,000 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Type A - Taxi | A12997444 | A12997543 | 100 | William Jones |
12/09/2024 16:41 | Acme insurance co. Ltd | Class A - PSV Unmarked | A13127787 | A13128086 | 300 | William Jones |
5000 | ||||||
declarations(from agents) | (errata here-its should be as agency/broker not insurance co. Ltd) | |||||
Transaction Date | Company Name | Certificate Type | First Serial # | Last Serial # | Qty(Out/In) | User Name |
12/09/2024 17:12 | Skym insurance co. Ltd | Type B - Commercial Vehicle | B13854311 | B13866236 | -50 | declarations |
12/09/2024 17:12 | VICT insurance co. Ltd | Type B - Commercial Vehicle | B13866237 | B13866266 | -30 | declarations |
12/09/2024 17:13 | Coun insurance co. Ltd | Type D - PSV | D5106559 | D5106574 | -16 | declarations |
Last edited: