vba - List excel pivot items of the second row field (multiple row fields) given a particular pivot item in the first row field -


i searched long time this, still inconclusive. have pivot table 2 row labels , 1 data variable looking like:

rowlabel1 rowlabel2 sum of value                      1               b          2               c          3    d          d          4    e          e          5

i want able list pivot items of rowlabel2 given particular rowlabel1, "a"" b" "c" in loop, concat "abc". whatever try, outputs of pivot items, "abcde".

since real data more complex, not possible manually. have 3 row labels rather 2 labels.

don't know if solution can make use of values. example, return rowlabel2 items value <= 3 when "letter" "a".

thanks input!!

sub getpiv()  dim piv pivottable  'change whatever range , sheet names pivottable set piv = thisworkbook.sheets("secondtable_output").range("a3").pivottable  dim r range dim dr pivotitem  'this captures first entry in rowlabel1 - change necessary set dr = piv.pivotfields(1).pivotitems(1)  dim str string str = ""  'the macro won't work if item collapsed, set showdetail true expand if dr.showdetail = false     dr.showdetail = true end if  dr     'address seems bug(??) if there more 1 data value columns,     'the datarange appears shift 1 row down     if .datarange.columns.count > 1         'here shift datarange 2nd column of pivottable         set r = .datarange.offset(-1, -(.datarange.cells(1, 1).column - 2))     else         'here shift datarange 2nd column of pivottable         set r = .datarange.offset(0, -(.datarange.cells(1, 1).column - 2))     end if end  'delete necessary r.select  'obtain string of cell values range = 1 r.rows.count     str = str & " " & r.cells(i, 1) next str = trim(str) msgbox str  end sub

Comments

Post a Comment

Popular posts from this blog

resizing Telegram inline keyboard -

telegram's inline keyboard great feature lots of different use cases. inline buttons added list of items this: inline_keyboard = [[inlinekeyboardbutton(text="button", callback_data="button"), inlinekeyboardbutton(text="reset",callback_data="reset")]] inline_keyboard_markup = inlinekeyboardmarkup(inline_keyboard) update.message.reply_text("hi", reply_markup=inline_keyboard_markup) the above code adds 2 buttons each half width of chat screen. i know the normal keyboard button there resize_keyboard parameter somehow can used. my question is there way resize inline buttons ? example make full width or quarter width. unfortunately, can't now. :( you can suggest @botsupport , might add feature next version.
Read more

recursion - Can every recursive algorithm be improved with dynamic programming? -

i first year undergraduate csc student looking competitive programming. recursion involves defining , solving sub problems. understand, top down dynamic programming (dp) involves memoizing solutions sub problems reduce time complexity of algorithm. can top down dp used improve efficiency of every recursive algorithm overlapping sub problems? dp fail work , how can identify this? the short answer is: yes. however, there constraints. obvious 1 recursive calls must overlap. i.e. during execution of algorithm, recursive function must called multiple times same parameters. lets truncate recursion tree memoization. can use memoization reduce number of calls. however, reduction of calls comes price. need store results somewhere. next obvious constraint need have enough memory. comes not-so obvious constraint. memory access requires time. first need find result stored , maybe copy location. in cases, might faster let recursion calculate result instead of loading somewher...
Read more

javascript - How to bind ViewModel Store to View? -

i'm pretty new ext js , trying embed multiselect inside panel . the viewmodel has stores property can see here: ext.define('test.view.controls.search.searchfiltermodel', { extend: 'ext.app.viewmodel', alias: 'viewmodel.filter', data: { title: '' }, stores: { test: { fields: [ 'id', 'name' ], proxy: { type: 'ajax', url: 'api/test', reader: 'array' }, autoload: true } } }); i bind in view this: viewmodel: { type: 'filter' }, layout: 'fit', border: 1, plain: true, scrollable: 'y', layout: 'fit', bind: { title: '{title}', }, items: { xtype: 'multiselect', scrollable: false, allowblank: true, ddreorder: true, bind: { store: '{test}' }, valuefield: 'id', displayfield: 'name' } ...
Read more