excel - VBA || Transpose unique values and Sums -


i'm trying make map transposes unique values column , populate new list parameters table,

table

the result on map should following

list

i've have code unique values follows:

dim d object dim c variant dim long dim lr long  set d = createobject("scripting.dictionary")     lr = cells(rows.count, 9).end(xlup).row     c = range("b2:b" & lr)     = 1 ubound(c, 1)     d(c(i, 1)) = 1      next range("ak2").resize(d.count) = application.transpose(d.keys)

although filling amounts on columns base , vat i'm having issues trying think on formula, "base" value should total document nr of accounts starting 6*,7* result of dr - cr.

i know may sound bit confusing, if please me appreciated.

using @ronrosenfeld formula comments, following might helpful:

sub demo()      dim lastrow long, lastcol long, currlr long     dim rng range, rngwh range     dim srcsht worksheet      application.screenupdating = false     application.calculation = xlcalculationmanual      set srcsht = sheets("sheet1")   'set data sheet here     srcsht         lastrow = .range("a" & .rows.count).end(xlup).row 'last row data in sheet         set rng = .range("a1:a" & lastrow)                'range filter         set rngwh = .range("a2:a" & lastrow)              'range formulas         lastcol = .cells(1, .columns.count).end(xltoleft).column + 2 'column display data         rng.advancedfilter action:=xlfiltercopy, copytorange:=.cells(1, lastcol), unique:=true         currlr = .cells(.rows.count, lastcol).end(xlup).row 'unique nr. doc count         lastcol = lastcol + 1          'formula base         .cells(1, lastcol).value = "base"         .range(.cells(2, lastcol), .cells(currlr, lastcol)).formula = _             "=sumproduct((" & .cells(2, lastcol - 1).address(false, false) & "=" & rngwh.address & ")*(left(" & rngwh.offset(, 1).address & ")={""6"",""7""})*(" & rngwh.offset(, 2).address & "))"          'formula vat         .cells(1, lastcol + 1).value = "vat"         'enter formula here vat          'formula total         .cells(1, lastcol + 2).value = "total"         .range(.cells(2, lastcol + 2), .cells(currlr, lastcol + 2)).formula = _             "=sumif(" & rngwh.address & "," & .cells(2, lastcol - 1).address(false, false) & "," & rngwh.offset(, 3).address & ")"          .range(.cells(2, lastcol), .cells(currlr, lastcol + 2)).value = .range(.cells(2, lastcol), .cells(currlr, lastcol + 2)).value     end      application.screenupdating = true     application.calculation = xlcalculationautomatic end sub

Comments

Post a Comment

Popular posts from this blog

python - Selenium remoteWebDriver (& SauceLabs) Firefox moseMoveTo action exception -

while performing simple hover test on firefox through selenium (3.4.0) python bindings (3.4.3) using of available os in saucelabs (except linux, latest available firefox version old), performing following actionchain: hover = actionchains(driver) hover.move_to_element(elm_men_menu).perform() it throws following error: taceback: file [...] in testhover hover.perform() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 80, in perform action() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 290, in <lambda> command.move_to, {'element': to_element.id})) file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/remote/webdriver.py", line 256, in execute self.error_handler.check_response(response) file "/usr/local/lib/python2.7/...
Read more

html - How to custom Bootstrap grid height? -

why bootstrap grid has height 591.067 ? did height came ? .main-post { padding: 20px; background-color: #fff; border: 1px solid #d8d8d8; margin-bottom: 100px; } .main-post .post-categories { margin-bottom: 10px } .main-post h3 { margin: 0 0 10px; color: #777; letter-spacing: -1px; font-weight: bold } .main-post .post-author, .main-post .post-date, .main-post .post-comments { font-size: 12px } .main-post img { display: block; margin: 10px 0; } .main-post .post-summary { line-height: 1.7; color: #888 } .main-post { color: #999 } <div class="col-sm-6"> <div class="main-post"> <h3 class="post-title"> <a href="#"> post title </a> </...
Read more

transpose - Maple isnt executing function but prints function term -

im using maple im in worksheetmode tried using maple input , 2d input , want transpose matrix a: := `<|>`(`<,>`(1, .5, -2), `<,>`(.5, 9/4+b, 5+3*b), `<,>`(-2, 5+3*b, 18+9*b+4*a)); b:= transpose(a); when exectute sheet dont translated values, there same input. matrix looks same input matrix plus function term. you can see picture in following link: why arent functions executed? meanwhile b:=a^+ doing right way , transposed matrix. other functions return function body instead needed values... if using 2d input mode (the default) space you've got between transpose , bracketed (b) interpreted multiplcation. rid of such space. also, either load package @ start of document like, with(linearalgebra): before calling transpose command package, or call it's full name like, linearalgebra:-transpose(b);
Read more