Telefoon rapportage vereenvoudige. van 3 regels naar 1 regel

[alletwee]

Hallo. Ik werk met een bestand waarin we elk telefoontje wat binnenkomt over 3 regels gerapporteerd krijgen.
Ik wil graag dat dit op 1 regel staat.
Ik kom er niet uit hoe ik dat voor elkaar moet krijgen. Wie kan me op weg helpen om dit opgelost te krijgen.
Ben nu iedere keer aan het knippen/plakken maar dat kost veel te veel tijd.
Ik heb een voorbeeld bijgevoegd als bijlage en aangegeven wat voor uitkomst ik zou willen hebben,

 

[AHulpje]

Probeer het hier eens mee (weet niet zeker of dat op een Mac ook werkt):

 

[alletwee]

dank je wel, dit werkt.
Kun je me ook uitleggen wat je gedaan hebt en hoe ik dit kan gebruiken?
Wil het graag leren.

 

[AHulpje]

Daar heb ik een VBA macro voor in elkaar geflanst, genaamd Rapportage. Die macro wordt gestart als je op de knop drukt, je kunt de macro ook starten via de tab Ontwikkelaars, Macro's. Je kunt de macro bekijken via Alt+F11, al weet ik niet of dat op een Mac ook zo gaat. Het voert te ver om hier een cursus VBA te geven. Je kunt ChatGPT.com vragen de code van uitleg te voorzien.

Sub Rapportage()
    Sheets("Blad1").Activate
    i = 0
    a = ActiveSheet.Range("A1").CurrentRegion
    ReDim b((UBound(a) - 1) / 3, 9)
    For r = 2 To UBound(a) Step 3
        a(r, 7) = a(r + 2, 6)
        a(r, 9) = a(r + 1, 6)
        For k = 0 To 8
            b(i, k) = a(r, k + 1)
        Next
        i = i + 1
    Next
    Sheets("Rapportage").Activate
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp
    Range("A2").Resize(UBound(b), 9) = b
    Range("D2:D" & UBound(b) + 1).NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
End Sub

 

[alletwee]

Dit is namelijk hoe het bestand er uit ziet als ik het download.
Normaal sla ik het eerst op als .XLSX en ga er dan mee aan het werk.
Wat ik uiteindelijk wil is dit bestand in een sheet plakken en dan de bewerking uitvoeren.
Ik krijg nu natuurlijk foutmeldingen.
Wil je er aub nog eens naar kijken?

 

[AHulpje]

Open het bestand met de macro.
Open het CSV bestand en doe er verder niets mee.
Blijf in het CSV bestand en draai de macro.

Bericht automatisch samengevoegd: Vandaag om 14:17

Er lijken rijen te ontbreken in het bronbestand.
Meestal is het tijdstip voor drie achtereenvolgende rijen gelijk, maar voor bijvoorbeeld de telefoontjes op de rijen 26 en 28 lijkt de derde rij te ontbreken.

 

[Warme bakkertje]

Dit wordt zeer moeilijk automatiseren aangezien de gegevens in je CSV bestand niet consistent zijn.
Je gaat ervan uit dat per drie regels de logica Inkomend - Inkomend - Uitgaand is.
Dat is zeker niet het geval in je CSV bestand en dan gaat het mis.

 

[alletwee]

Klopt. Dat is het probleem waar ik telkens tegenaan loop.
Ik kan dit wel op lossen denk ik door een lijst toe te voegen van de doorschakelnummer? Als het nummer dan in deze lijst voor komt is het daadwerkelijk een nummer waarna is doorgeschakeld en het andere nummer het gebelde numnmer.

 

[AHulpje]

Het lijkt erop dat in je voorbeeldbestand 124 keer de uitgaand regel ontbreekt. Daar valt wel iets op te verzinnen in de macro. Wat moet er in kolom G (Doorgeschakeld naar) ingevuld worden als de rij met Uitgaand ontbreekt?

 

[alletwee]

Vul dan maar in 0000000000

 

[AHulpje]

Probeer het hier eens mee, volgens de methode van #6.

 

[alletwee]

Dit begrijp ik niet
Hoe "draai" ik de macro

Open het bestand met de macro.
Open het CSV bestand en doe er verder niets mee.
Blijf in het CSV bestand en draai de macro.

 

[AHulpje]

To run an Excel macro on a Mac, first ensure the Developer tab is enabled by going to Excel > Preferences > Ribbon & Toolbar and checking the Developer box. Then, open your workbook, select the Developer tab, click on Macros, choose the macro you want to run, and press the Run button.

 

[snb]

Zet de macro in een leeg excelbestand.
Pas het pad van het csv-bestand aan.

Sub M_snb()
   sn = Split(CreateObject("scripting.filesystemobject").opentextfile("D:\downloads\cdr_exporteren_50080_275430_raw.csv").readall, vbLf)
   sn(0) = Replace(sn(0), "Bestemming", "Bestemming;Doorschakeling")
 
   For j = 1 To UBound(sn) - 1
     st = Split(sn(j), ";")
     st(9) = st(8)
   
     For jj = 1 To 2
        sq = Split(sn(j + jj), ";")
        If CDate(st(4) & " " & st(5)) = CDate(sq(4) & " " & sq(5)) Then
           If jj = 1 Then st(17) = sq(7)
           If jj = 2 Then st(8) = sq(7)
           sn(j + jj) = "~"
        Else
           st(8) = ""
           Exit For
        End If
     Next
     sn(j) = Join(st, ";")
     j = j + jj - 1
   Next

   sn = Filter(sn, "~", 0)
   Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub

 

[AHulpje]

@snb
Prachtig hoor, hoewel moeizaam leesbaar, maar helaas kun je geen filesystemobject gebruiken op een Mac.

 

[snb]

Vermijd Select en Activate in VBA. Die zijn volstrekt overbodig, vanwege VBAs OOP-karakter.

 

[JEC.]

Zou zeker voor Power Query gaan. Veel beter te volgen. Power Query kun je op een mac als add-in installeren. Zoek daar eens op. Pas het pad in de Source regel aan. De output komt automatisch netjes in een tabel.

let
    Source = Csv.Document(File.Contents("C:\Users\xxxxx\Downloads\cdr_exporteren_50080_275430_raw (1).csv"),[Delimiter=";", Columns=21, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    delCols = Table.SelectColumns(headers,{"UniqueId", "Klantnummer", "Datum", "Tijdstip", "Afzender", "Bestemming", "DoorgeschakeldNaar","Duur", "Extensie"},MissingField.UseNull),
    groupCols = {"Klantnummer", "Datum", "Tijdstip", "Afzender", "Duur"},
    group = Table.Group(delCols, groupCols, {{"UniqueId", each List.First([UniqueId])},{"Bestemming", each List.First([Bestemming]) },{"DoorgeschakeldNaar", each List.Last([Bestemming]) },{"Extensie", each List.Max([Extensie]) }}),
    reorder = Table.ReorderColumns(group,Table.ColumnNames(delCols)),
    types = Table.TransformColumnTypes(reorder,{{"Datum", type date}, {"Tijdstip", type time}, {"Duur", Int64.Type}, {"Extensie", Int64.Type}})
in
    types