Insert new row for breakdown detail data

[kimbenz]

Good Day Mates,


I'd like to seek your help for my problem, as attached in file.


I have Raw Data in row 2 - 6 and my expectation is to breakdown each raw data by REMARKS INVOICE PAYMENT as can be seen in row 9 - 17.
If Remarks Invoice Payment contain more than one invoice than it need to be insert as new row, and also including the amount of invoice which contain in between the brackets "( )".


Highly appreciated for the feedback. Thank you!


Cheers!

[mana]

Sub test()
    Dim dic As Object
    Dim tbl As Range
    Dim k As Long, j As Long
    Dim v, ss, s
    
    Set dic = CreateObject("scripting.dictionary")
    Set tbl = Cells(1).CurrentRegion
    
    For k = 3 To tbl.Rows.Count
        v = tbl.Rows(k).Value
        ss = Split(v(1, 7), ")")
        For j = 0 To UBound(ss) - 1
            s = Split(ss(j), "(")
            v(1, 7) = s(0)
            v(1, 9) = s(1)
            dic(dic.Count) = v
        Next
    Next
    
    Worksheets.Add.Cells(1).Resize(dic.Count, 9).Value = _
        Application.Index(dic.items, 0, 0)

End Sub