excel - Split delimited 2nd and 3rd column data into new rows -

i have following table

  id.      id2.              string   123.     567, 986          abc;bcd;acd   142.     134, 654,1134     aa;bb 

i want displayed

 id   id2  string  123  567  abc  123  986  bcd  123       acd  142  134  aa  142  654  bb  142  1134 

the values in id column unique.

is there efficient macro solution this? have huge set of data.

with starting, concatenated data in active sheet , id in a1, run macro.

sub split_out()     dim v long, vvals variant, vid2s variant, vstrs variant     dim rw long, lr long, mx long      activesheet         lr = .cells(rows.count, 1).end(xlup).row         .cells(1, 1).currentregion.rows(1).copy destination:=.cells(lr + 2, 1)         rw = 2 lr             vvals = application.index(.cells(rw, 1).resize(1, 3).value, 1, 0)             vid2s = split(vvals(2), chr(44))             vstrs = split(vvals(3), chr(59))             mx = application.max(ubound(vid2s), ubound(vstrs))             v = lbound(vid2s) mx                 .cells(rows.count, 1).end(xlup).offset(1, 0) = vvals(1)                 if ubound(vid2s) >= v _                     .cells(rows.count, 1).end(xlup).offset(0, 1) = vid2s(v)                 if ubound(vstrs) >= v _                     .cells(rows.count, 1).end(xlup).offset(0, 2) = vstrs(v)             next v         next rw     end  end sub 

the flattened data populated below existing data. results should similar following.