| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate On Steroids???
It seems very difficult to accomplish what am after with builtin
Consolidate or PivotTable tools...they work to accumulate (say) totals on numeric data for matching areas but aren't so helpful to try to actually summarize a data table with related subsections by an ID to produce a new sheet that is the SUM() of the related numeric values but also contains the other, non-numeric data of the worksheet... Example Investment Pool 1 Fund Name CorpusValue CurrentValue SomePertinentComments DonorA XXX YYY Fund is for ... DonorB XXX YYY Fund is for ... DonorC XXX YYY Fund is for ... DonorD XXX YYY Fund is for ... DonorE XXX YYY Fund is for ... DonorF XXX YYY Fund is for ... Total Pool1 SSS SSS Investment Pool 2 Fund Name CorpusValue CurrentValue SomePertinentComments DonorC XXX YYY Fund is for ... DonorE XXX YYY Fund is for ... DonorG XXX YYY Fund is for ... DonorH XXX YYY Fund is for ... Total Pool2 SSS SSS Investment Pool 3 Fund Name CorpusValue CurrentValue SomePertinentComments .... DonorQ XXX YYY Fund is for ... Total Pool3 SSS SSS Grand Total SSS SSS As can be observed, there are Funds of Donors C and E in both Pools 1 and 2; for accounting reasons these must be kept independently but it is also needed to combine the totals for each unique fund; as noted a pivot table can (with effort) be set up to do the numbers but it isn't able to bring over the non-numeric field associated with the first appearance of the particular donor and thus automagically build the other worksheet. Is there any clever way to accomplish this other than VBA or tedious VLOOKUP() kinds of things? Actually, VBA is probably not _too_ bad if one knows anything useful of syntax...that leaves me mostly out, I can write a MATLAB external processor in a couple minutes, but the Foundation doesn't have MATLAB at hand... I've tried several passes at it with pivot tables and consolidate this afternoon; come up lacking so far, hence the question. -- |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consolidate On Steroids???
It seems very difficult to accomplish what am after with builtin Consolidate
or PivotTable tools...they work to accumulate (say) totals on numeric data for matching areas but aren't so helpful to try to actually summarize a data table with related subsections by an ID to produce a new sheet that is the SUM() of the related numeric values but also contains the other, non-numeric data of the worksheet... Example Investment Pool 1 Fund Name CorpusValue CurrentValue SomePertinentComments DonorA XXX YYY Fund is for ... DonorB XXX YYY Fund is for ... DonorC XXX YYY Fund is for ... DonorD XXX YYY Fund is for ... DonorE XXX YYY Fund is for ... DonorF XXX YYY Fund is for ... Total Pool1 SSS SSS Investment Pool 2 Fund Name CorpusValue CurrentValue SomePertinentComments DonorC XXX YYY Fund is for ... DonorE XXX YYY Fund is for ... DonorG XXX YYY Fund is for ... DonorH XXX YYY Fund is for ... Total Pool2 SSS SSS Investment Pool 3 Fund Name CorpusValue CurrentValue SomePertinentComments ... DonorQ XXX YYY Fund is for ... Total Pool3 SSS SSS Grand Total SSS SSS As can be observed, there are Funds of Donors C and E in both Pools 1 and 2; for accounting reasons these must be kept independently but it is also needed to combine the totals for each unique fund; as noted a pivot table can (with effort) be set up to do the numbers but it isn't able to bring over the non-numeric field associated with the first appearance of the particular donor and thus automagically build the other worksheet. Is there any clever way to accomplish this other than VBA or tedious VLOOKUP() kinds of things? Actually, VBA is probably not _too_ bad if one knows anything useful of syntax...that leaves me mostly out, I can write a MATLAB external processor in a couple minutes, but the Foundation doesn't have MATLAB at hand... I've tried several passes at it with pivot tables and consolidate this afternoon; come up lacking so far, hence the question. Did you try it using SUMIF()? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consolidate On Steroids???
On 2/18/2018 3:47 PM, GS wrote:
It seems very difficult to accomplish what am after with builtin Consolidate or PivotTable tools...they work to accumulate (say) totals on numeric data for matching areas but aren't so helpful to try to actually summarize a data table with related subsections by an ID to produce a new sheet that is the SUM() of the related numeric values but also contains the other, non-numeric data of the worksheet... Example Investment Pool 1 Fund Name** CorpusValue* CurrentValue* SomePertinentComments DonorA******* XXX************ YYY****** Fund is for ... DonorB******* XXX************ YYY****** Fund is for ... DonorC******* XXX************ YYY****** Fund is for ... DonorD******* XXX************ YYY****** Fund is for ... DonorE******* XXX************ YYY****** Fund is for ... DonorF******* XXX************ YYY****** Fund is for ... Total Pool1** SSS************ SSS Investment Pool 2 Fund Name** CorpusValue* CurrentValue* SomePertinentComments DonorC******* XXX************ YYY****** Fund is for ... DonorE******* XXX************ YYY****** Fund is for ... DonorG******* XXX************ YYY****** Fund is for ... DonorH******* XXX************ YYY****** Fund is for ... Total Pool2** SSS************ SSS Investment Pool 3 Fund Name** CorpusValue* CurrentValue* SomePertinentComments ... DonorQ******* XXX************ YYY****** Fund is for ... Total Pool3** SSS************ SSS Grand Total** SSS************ SSS As can be observed, there are Funds of Donors C and E in both Pools 1 and 2; for accounting reasons these must be kept independently but it is also needed to combine the totals for each unique fund; as noted a pivot table can (with effort) be set up to do the numbers but it isn't able to bring over the non-numeric field associated with the first appearance of the particular donor and thus automagically build the other worksheet. Is there any clever way to accomplish this other than VBA or tedious VLOOKUP() kinds of things?* Actually, VBA is probably not _too_ bad if one knows anything useful of syntax...that leaves me mostly out, I can write a MATLAB external processor in a couple minutes, but the Foundation doesn't have MATLAB at hand... I've tried several passes at it with pivot tables and consolidate this afternoon; come up lacking so far, hence the question. Did you try it using SUMIF()? That's the same thing; it can pull together the sums but not build the desired other merged worksheet. Let me amplify a little on the problem I'm trying to solve for more context... Allowable spending from each fund is computed at each year end based on value for each fund in each pool with rules that are Pool-dependent owing to various accounting rules--those aren't particularly pertinent here but is part of reason there needs must be the separate pools to begin with; it isn't just arbitrary but legal requirement. Once the funding level is set for the year, then the process of making awards to eligible students based on the Donor Criteria for each fund begins; this is something that happens over the year and requires matching up those requirements to the pool of applicants. The problem is that there may be $558 in Pool 1 and another $138 in Pool 2 for a given Fund; as is it is very tedious to get those together albeit simply a consolidation does that one step. But, what would be _much_ easier since this process isn't just a "one and done" but is revisited many times over the course of an academic year, would be to have the above sheet of accounting-purpose information moved over to another "DISBURSEMENTS" sheet that combines the funds and eliminates the redundant entries. IOW, the result I'd like would look like Approved Spending Year YYYY Fund Name SpendAllow Awarded Awardee/Comments DonorA XXX YYY Fund is for ... DonorB XXX YYY Fund is for ... DonorC XXX YYY Fund is for ... DonorD XXX YYY Fund is for ... DonorE XXX YYY Fund is for ... DonorF XXX YYY Fund is for ... DonorG XXX YYY Fund is for ... DonorH XXX YYY Fund is for ... .... DonorQ XXX YYY Fund is for ... Total SSS SSS where all the funds of the same are combined with duplicate entries removed and the SpendAllow value is the total of each Pool for the given fund. Awarded will be filled in by the awards committee and then there's another set of columns for actual recording of payouts monthly. That help? --dpb |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consolidate On Steroids???
On 2/18/2018 4:18 PM, dpb wrote:
.... "DISBURSEMENTS" sheet that combines the funds and eliminates the redundant entries.* IOW, the result I'd like would look like Approved Spending Year YYYY Fund Name** SpendAllow* Awarded* Awardee/Comments DonorA******* XXX******** YYY****** Fund is for ... DonorB******* XXX******** YYY****** Fund is for ... DonorC******* XXX******** YYY****** Fund is for ... DonorD******* XXX******** YYY****** Fund is for ... DonorE******* XXX******** YYY****** Fund is for ... DonorF******* XXX******** YYY****** Fund is for ... DonorG******* XXX******** YYY****** Fund is for ... DonorH******* XXX******** YYY****** Fund is for ... ... DonorQ******* XXX******** YYY****** Fund is for ... Total******** SSS******** SSS where all the funds of the same are combined with duplicate entries removed and the SpendAllow value is the total of each Pool for the given fund.* Awarded will be filled in by the awards committee and then there's another set of columns for actual recording of payouts monthly. That help? --dpb Also, it needs to be dynamic because new funds are added annually and pools also can come and go; some of the pools are temporary for things like DOE Title III, V matching grants that have 20-yr restrictions on them after which the restrictions expire and the funds may be rolled over into the primary investment pool. Thus the number of funds and pools can be different every year so simply creating a static set of descriptions doesn't work well. -- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consolidate On Steroids???
OK, this is a perfect task for SUMIF() because fund names are duplicated in the
various pools, but will be totalled in a single record on your 'consolidation' sheet. The *key* to making this possible is to use a 'structured' template for the consolidation sheet, and VBA to auto-populate it with the appropriate data. This is not a daunting task for most Excel programmers since this is a very common type of task. The repetative nature of the task sets the prerequiste for using a structured template designed to meet your 'report' needs, which can be inserted into any workbook where needed. The easy part is that your consolidation sheet (I call it "Summary" in my projects) only has to pull data from 1 sheet (I assume), not pull from several sheets. Perhaps we could collaborate on this project so you get started in this concept with some measure of comfort? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Consolidate On Steroids???
On 2/18/2018 5:06 PM, GS wrote:
OK, this is a perfect task for SUMIF() because fund names are duplicated in the various pools, but will be totalled in a single record on your 'consolidation' sheet. The *key* to making this possible is to use a 'structured' template for the consolidation sheet, and VBA to auto-populate it with the appropriate data. This is not a daunting task for most Excel programmers since this is a very common type of task. The repetative nature of the task sets the prerequiste for using a structured template designed to meet your 'report' needs, which can be inserted into any workbook where needed. The easy part is that your consolidation sheet (I call it "Summary" in my projects) only has to pull data from 1 sheet (I assume), not pull from several sheets. Perhaps we could collaborate on this project so you get started in this concept with some measure of comfort? I sorta' grok the idea; SUMIF() didn't precisely come to mind other than for the numerics it certainly can compute the desired totals...I was trying to figure out there was surely some way to merge with data tables or the like; seemed like precisely what something like the CONSOLIDATE engine out to be built to do... What is your idea of how to "collaborate" here? I've moaned about learning the VBA syntax for all the stinkin' Excel objects before...it's trivial to pull the data out and use a real programming language on it and put it back, but that only works while I'm around to do it, unfortunately. -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct formula on steroids | Excel Worksheet Functions | |||
| How to Improve my "Vlookup" on Steroids function..... | Excel Programming | |||
| consolidate | Excel Discussion (Misc queries) | |||
| Consolidate | Excel Discussion (Misc queries) | |||
| Cursor gone beserk, apparent overdose of steroids! | Excel Programming | |||
Hybrid Mode
