excel - Arranging collapsed data in a table -

im having collapsed data shown below, have id & header below header value exists. each id has own different no of headers & values.

i have arrange them table have consolidated headers of ids 1 row , ids in 1 column. based 1 id need update respective header's value below.

id--+--h1--+--h2--+--h3--+--h4--+--h5--| 18219--v1--+--v3--+--  --+--  --+--  --|     18218--v2--+--v4--+--  --+--  --+--  --| 18217--v1--+--v2--+--v3--+--v4--+--v5--| 

can me out?

try code:

option explicit  sub consolidate()      dim arrcontent variant     dim strsource string     dim strdest string     dim x long     dim y long     dim p long     dim objheader object     dim objitem variant     dim lngcolscount long      ' set initial values     strsource = "source" ' source worksheet name     strdest = "destination" ' destination worksheet name     y = 1 ' source worksheet first id cell's row number     x = 2 ' source worksheet first id cell's column number     set objheader = createobject("scripting.dictionary")      ' pack source data array of dictionaries     objheader.add "id", 0     arrcontent = array()     sheets(strsource)         while .cells(y, x).value <> "" , .cells(y + 1, x).value = ""             set objitem = createobject("scripting.dictionary")             objitem.add 0, .cells(y, x).value             p = x + 1             while .cells(y, p).value <> ""                 if not objheader.exists(.cells(y, p).value) objheader.add .cells(y, p).value, objheader.count                 objitem(objheader(.cells(y, p).value)) = .cells(y + 1, p).value                 p = p + 1             loop             redim preserve arrcontent(ubound(arrcontent) + 1)             set arrcontent(ubound(arrcontent)) = objitem             y = y + 2         loop     end      ' output     sheets(strdest)         .cells.delete         lngcolscount = ubound(objheader.keys)         .range(.cells(1, 1), .cells(1, lngcolscount + 1)).value = objheader.keys         y = 2         each objitem in arrcontent             x = 1 lngcolscount + 1                 .cells(y, x).value = objitem(x - 1)             next             y = y + 1         next     end  end sub 

for source table:

it generates output: