Excel array formula to sum for criteria then sort -


looking excel array formula sum "weight" each "code" , sort "code"s in order of weight.

    |     |    b   |   c   |   d   |    e    |  |1 |  code | weight |       |  code |totweight|  |2 |  444  |   30   |       |  444  |   50    |  |3 |  333  |   10   |       |  222  |   40    |      |4 |  222  |   10   |       |  333  |   40    |  |5 |  444  |   10   |       |  555  |   20    |          |6 |  444  |   10   |  |7 |  222  |   30   |  |8 |  333  |   20   |  |9 |  555  |   20   |  |10|  333  |   10   |

cols , b inputs, d , e outputs.

while may possible convoluted , calculation-intensive array formula, intents , purposes trying accomplish database select statement aggregate sum, group , order clauses. like,

select code, weight  (select cw.code, sum(cw.weight) weight   tblcodeweights cw   group cw.code) order weight desc, code;

i suggest vba sub procedure utilizing adodb.connection worksheet can produce results trying achieve. following code verbose , trimmed down little every facet of operation handled , should self-explanatory or researchable.

option explicit  sub sortedfilteredsums()     dim cnx object, rs object     dim sws1 string, swb string, scnx string, ssql string     dim ws1tbladdr string      worksheets("sheet8")         ws1tbladdr = .cells(1, 1).currentregion.address(0, 0)         sws1 = worksheets("sheet8").name     end      swb = thisworkbook.fullname     scnx = "provider=microsoft.jet.oledb.4.0;data source=" & swb _         & ";extended properties=""excel 8.0;hdr=yes;imex=1"";"     debug.print scnx      set cnx = createobject("adodb.connection")     set rs = createobject("adodb.recordset")      cnx.open scnx      ssql = "select [code], [weight] ("     ssql = ssql & " select cw.[code], sum(cw.[weight]) [weight]"     ssql = ssql & " [" & sws1 & "$" & ws1tbladdr & "] cw"     ssql = ssql & " group cw.code"     ssql = ssql & ") order [weight] desc, [code]"      debug.print ssql     'select code, weight     ' (select cw.code, sum(cw.weight) weight     '  tblcodeweights cw     '  group cw.code)     'order weight desc, code;      rs.open ssql, cnx      worksheets("sheet8")         .range("d1").resize(1, 2) = array("code", "totweight")         .range("d2").copyfromrecordset rs     end      rs.close: set rs = nothing     cnx.close: set cnx = nothing  end sub

please note cannot performed on workbook has not been saved; e.g. not on new untitled workbook. results should similar following.

sum_group_order

the test workbook used example temporarily available from:

ado select sum group by.xlsb


-

Comments

Post a Comment

Popular posts from this blog

php - isset function not working properly -

the page unable see login form code written under isset function statement. have written code correctly , have executed many times , code written inside isset statement not works. here code:- <?php session_start(); echo "<p style=\"font-color: #ff0000;\"> catogoies </p>"; echo '<link href="var/www/html/sample.css" rel="stylesheet">'; require_once('../html/conn.php'); $query = "select * catogories"; mysqli_select_db($dbc, 'odit'); $retrieve = mysqli_query($dbc, $query); if(!$retrieve) { die(mysqli_error($query)); } while($row=mysqli_fetch_array($retrieve, mysql_assoc)){ echo "<p style=\"font-color: #ff0000;\">".'<a href="cats.php? catogory='.urldecode($row["name"]).'">'.$row["name"].'</a>'."</p>"; $_session['cat']=$row["name"]; } if(!($_session)) ...
Read more

javascript - Thinglink image not visible until browser resize -

Image
i trying repurpose pre-existing theme, includes drop down menu displays image. trying embed thinglink drop down menu's content section, not visible until resize browser window. here menu looks like: then when clicked, can see empty space image should show: then when browser resized, image appears: this code content of menu: <div class="ajax_accordion_content" style="display: none;"> <div class="report-detail"> <table border="0" cellpadding="0" cellspacing="0" width="100%" class="table_basic"> <tbody> <!-- thinglink image embedded --> <img style="max-width: 100%;" src="http://cdn.thinglink.me/api/image/832705713204625409/1024/10/scaletowidth#tl-832705713204625409;1043138249'" class="alwaysthinglink"/> <script async charset="utf-8" src="http://cdn.thinglink.me/jse/embed.j...
Read more

firebird - Error "invalid transaction handle (expecting explicit transaction start)" executing script from Delphi -

i'm developing update app delphi 10. i'm running windows 7 64x, firebird 2.5.1.26351 32x. execution order: checks current version(select statement firebird database) downloads update(via ftp) apply it(.exe files, , execute .sql) error code -901 pops out when try execute .sql files. know there incompatibility issue w7 64x , fdb 32x, but, can everything: backup , restore(gbak), db commands(all). tried fdb 64x instead, , not working. code: //components fdwaitcursor: tfdguixwaitcursor; fddriverlink: tfdphysfbdriverlink; fdconnection:tfdconnection; fdscript: tfdscript; function tthr_script.createcomponents:boolean; begin try fddriverlink := tfdphysfbdriverlink.create(fddriverlink); fdwaitcursor := tfdguixwaitcursor.create(fdwaitcursor); fdconnection := tfdconnection.create(fdconnection); fdconnection begin drivername := 'fb'; params.username := thrbanco.bduser; params.password := thrbanco.bdpass; params.database := ...
Read more