vba - Transform Excel data into custom format -

i'm attempting transform large excel input table custom format, excel table. visually need accomplish:

the psuedo-code i've come solve problem goes follows:

2. pseudo code:

// column of higher level column b

initialize dictionary read entire spreadsheet system memory      while(sheet has records) {          loop through spreadsheet records top-down         start @ cell a1          immediate right of column              if(a:b not in dictionary){               dictionary>> append b child of in dictionary // must find correct entry , append value              }         move cell a+1         }     once sheet out of records {             move 1 column right             repeat while method             until entire column null      }      while (dictionary has records) {        key = column value        list of values = column b value        save values new excel sheet      }     end 

i'm not sure if there libraries exist accomplish need here, can use whatever language offers solution.

appreciate input all.

stuff 'raw data' 2 dimensional variant array , cycle through each rank, building children of parent or sub-parent case may be.

sub collate_family_values()     dim v long, w long, vvals variant     dim spar string, stmp string      activesheet    '<-set worksheet reference properly!         .columns("f:g").entirecolumn.delete         .cells(1, 6) = "output data"         .cells(2, 6).resize(1, 2) = .cells(2, 1).resize(1, 2).value          vvals = application.transpose(.range(.cells(3, 1), .cells(rows.count, 4).end(xlup)).value)          w = lbound(vvals, 1) ubound(vvals, 1) - 1             stmp = chrw(8203)             spar = vvals(w, lbound(vvals, 2))             v = lbound(vvals, 2) ubound(vvals, 2)                 if not cbool(instr(1, stmp, chrw(8203) & vvals(w + 1, v) & chrw(8203), vbtextcompare))                     stmp = stmp & vvals(w + 1, v) & chrw(8203)                 end if                 if spar <> vvals(w, application.min(v + 1, ubound(vvals, 2))) or v = ubound(vvals, 2)                     .cells(rows.count, 6).end(xlup).offset(1, 0).resize(1, 2) = _                       array(spar, replace(mid(stmp, 2, len(stmp) - 2), chrw(8203), ", "))                     stmp = chrw(8203)                     if v < ubound(vvals, 2) spar = vvals(w, v + 1)                 end if             next v         next w     end end sub 

i moved 'output dat' results right of 'raw data' due unknown number of rows adjust for.