excel - Calculate standard deviation of same text values in same column -

i trying write macro in excel calculate standard deviation of same text in column taking values column b , giving results in column c:

i did manually putting equation=stdev.s(a2;a3;a4;a16)for "aaa". need automatically because doing calculation , procedures completing macros. here code:

option explicit sub main()     collectarray "a", "d"     dosum "d", "e", "a", "b" end sub   ' collect array specific column , print new 1 without duplicates ' params: '           fromcolumn - column need remove duplicates '           tocolumn - reprint array without duplicates sub collectarray(fromcolumn string, tocolumn string)      redim arr(0) string      dim long     = 1 range(fromcolumn & rows.count).end(xlup).row         arr(ubound(arr)) = range(fromcolumn & i)         redim preserve arr(ubound(arr) + 1)     next     redim preserve arr(ubound(arr) - 1)     removeduplicate arr     range(tocolumn & "1:" & tocolumn & range(tocolumn & rows.count).end(xlup).row).clearcontents     = lbound(arr) ubound(arr)         range(tocolumn & + 1) = arr(i)     next end sub   ' sums values 1 column against other column ' params: '           fromcolumn - column string match against '           tocolumn - sum printed '           originalcolumn - original column including duplicate '           valuecolumn - column values sum private sub dosum(fromcolumn string, tocolumn string, originalcolumn string, valuecolumn string)     range(tocolumn & "1:" & tocolumn & range(tocolumn & rows.count).end(xlup).row).clearcontents     dim long     = 1 range(fromcolumn & rows.count).end(xlup).row         range(tocolumn & i) = worksheetfunction.sumif(range(originalcolumn & ":" & originalcolumn), range(fromcolumn & i), range(valuecolumn & ":" & valuecolumn))     next end sub   private sub removeduplicate(byref stringarray() string)     dim lowbound$, upbound&, a&, b&, cur&, temparray() string     if (not stringarray) = true exit sub     lowbound = lbound(stringarray): upbound = ubound(stringarray)     redim temparray(lowbound upbound)     cur = lowbound: temparray(cur) = stringarray(lowbound)     = lowbound + 1 upbound         b = lowbound cur             if lenb(temparray(b)) = lenb(stringarray(a))                 if instrb(1, stringarray(a), temparray(b), vbbinarycompare) = 1 exit             end if         next b         if b > cur cur = b         temparray(cur) = stringarray(a)     next     redim preserve temparray(lowbound cur): stringarray = temparray end sub 

it nice if please give me idea or solution. above code calculating summation of same text values. there way modify code calculate standard deviation?

here formula , vba route gives stdev.s each set of items.

picture shows various ranges , results. input same yours, accidentally sorted @ 1 point don't line up.

some notes

• array actual answer want. non-array showing later.
• i included pivottable test accuracy of method.
• vba same answer array calculated udf used elsewhere in vba.

formula in cell d3 array formula entered ctrl+shift+enter. same formula in e3 without array entry. both have been copied down end of data.

=stdev.s(if(b3=$b$3:$b$21,$c$3:$c$21)) 

since seems need vba version of this, can use same formula in vba , wrap in application.evaluate. pretty how @jeeped gets answer, converting range values meet criteria.

vba code uses evaluate process formula string built ranges given input.

public function stdev_s_if(rng_criteria range, rng_criterion range, rng_values range) variant      dim str_frm string      'formula reproduce     '=stdev.s(if(b3=$b$3:$b$21,$c$3:$c$21))      str_frm = "stdev.s(if(" & _         rng_criterion.address & "=" & _         rng_criteria.address & "," & _         rng_values.address & "))"      'if have more 1 sheet, sure evalutes in right context     'or add sheet name references above     'single sheet works fine application.evaluate      'stdev_s_if = application.evaluate(str_frm)     stdev_s_if = sheets("sheet2").evaluate(str_frm)  end function 

the formula in f3 vba udf of same formula above, entered normal formula (although entering array not affect anything) , copied down end.

=stdev_s_if($b$3:$b$21,b3,$c$3:$c$21) 

it worth noting .evaluate processes correctly array formula. can compare against non-array column included in output. not how excel knows treat way. there a extended conversion how evaluate process array formulas , determines output. tangentially related conversation.

and completeness, here test of sub side of things. running code in module sheet other sheet2 active. emphasizes ability of using sheets("sheets2").evaluate multi-sheet workbook since range call technically misqualified. console output included.

sub test()      debug.print stdev_s_if(range("b3:b21"), range("b3"), range("c3:c21"))     'correctly returns  206.301357242263  end sub