excel vba - VBA to insert rows between similar groups, add header and calculate geomean -

excel vba - VBA to insert rows between similar groups, add header and calculate geomean -

i have raw info imported text file in form:

current table: column1 | column2 | column3 info | value1 | value2 case1_int_a | 1 | 0.76 case1_int_b | 2 | 1.24 case1_fp_x | 3 | 2.00 case1_fp_y | 4 | 3.42 case1_fp_z | 43 | 1.876 case2_int_c | 100 | 0.234 case3_int_d | 12 | 1 case3_int_e | 134 | 1.6 desired table: column | column2 | column3 info | value1 | value2 case1_int_a | 1 | 0.76 case1_int_b | 2 | 1.24 geomean | = geo(..) | =geo(..) info | value1 | value2 case1_fp_x | 3 | 2.00 case1_fp_y | 4 | 3.42 case1_fp_z | 43 | 1.876 geomean | = geo(..) | =geo(..) info | value1 | value2 case2_int_c | 100 | 0.234 case3_int_d | 12 | 1 geomean | = geo(..) | =geo(..) info | value1 | value2 case3_int_e | 134 | 1.6 geomean | = geo(..) | =geo(..)

i tried utilize autofilter need hardcode criteria , since there many different types of groups, there should other way in can compare first 9 characters of column , insert blank rows. hope problem clear. in advance

this should output want. not elegant, should job done. info has cell a1 down. outputs columns f h.

sub cleanup() dim row1(3) string dim datavalue() string dim columnnum integer dim datarange range dim valuevalues() dim partition() integer columnnum = application.counta(range("a:a")) - 1 redim datavalue(columnnum) redim valuevalues(3, columnnum) set datarange = range("a2:a" & columnnum + 1) row1(1) = range("a1").value row1(2) = range("b1").value row1(3) = range("c1").value = 0 s = 0 'populate arrays redim preserve partition(1) partition(1) = 1 s = 1 each cell in datarange.cells = + 1 datavalue(i) = left(cell.value, len(cell.value) - 2) if > 1 if datavalue(i) <> datavalue(i - 1) s = s + 1 redim preserve partition(s + 1) partition(s) = end if end if valuevalues(1, i) = cell.value valuevalues(2, i) = cell.offset(0, 1).value valuevalues(3, i) = cell.offset(0, 2).value next cell 'output n = 0 t = -2 partition(s + 1) = columnnum + 1 m = 2 s + 1 t = t + 3 = 0 num = t cells(num, 5).value = row1(1) cells(num, 6).value = row1(2) cells(num, 7).value = row1(3) n = partition(m - 1) partition(m) - 1 = + 1 cells(num + i, 5).value = valuevalues(1, n) cells(num + i, 6).value = valuevalues(2, n) cells(num + i, 7).value = valuevalues(3, n) t = t + 1 next n cells(t + 1, 5).value = "geomean" cells(t + 1, 6).formula = "=geomean(f" & t - + 1 & ":f" & t & ")" cells(t + 1, 7).formula = "=geomean(g" & t - + 1 & ":g" & t & ")" next m end sub

excel-vba