excel - Adding to an array in VBA with strings as the index -

not sure i've labelled correctly.

i have bunch of cells containing strings of data. each cell consists of this:

q1 = 1   | q2 = 3.2 | q3 = 5.6 q1 = 1.8 | q3 = 2.1 | q5 = 1.4 

*note: delimiter litteral, text in single cell, pipe char.

i want loop through each cell, explode (to use php term) pipe (|) delimiter, , again = sign.

i want create array each possible value left of equal sign, , add value found right array (not add in sum, add in append array).

visually, think array should this:

vars[ q1 [ 1,1.8 ], q2 [ 3.2 ], q3 [ 5.6,2.1]....]  

end goal being i'd average, mean , median each of q1, q2 , q3.

is doable in vb? i'm more familiar php, keep in excel.

thanks.

this handle arbitrary number of "keys" (q1,q2, etc)

sub tester()  'needs reference microsoft scripting runtime dim d new scripting.dictionary  dim c range dim arrp, arre dim q, v, tmpv, tmpp, tmparr, ub dim long, n long dim k      each c in selection.cells         tmpv = trim(c.value)          if instr(tmpv, "=") > 0             arrp = split(tmpv, "|") 'split on pipe              = lbound(arrp) ubound(arrp)                 tmpp = arrp(i)                  if instr(tmpp, "=") > 0                     q = trim(split(tmpp, "=")(0))                     v = trim(split(tmpp, "=")(1))                      if isnumeric(v)                         if not d.exists(q)                             d.add q, array(v)                         else                             tmparr = d(q) 'get dict value temp array                             ub = ubound(tmparr) + 1                             redim preserve tmparr(0 ub) 'extend array                             tmparr(ub) = v                             d(q) = tmparr 'put dict                         end if                     end if                 end if             next          end if 'cell has @ least 1 "="      next c      'dump dictionary immediate pane     each k in d.keys         debug.print k, join(d(k), ",")     next k   end sub