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