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.

--

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

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.

--

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 would need a workbook from you with sample (dummy) of every conceivable type
of input (source) data the project may encounter on 1 sheet, and a sample
consolidate sheet to build the Summary template. From there I'll mock up an app
and send it for your review/feedback; -this will be a back&forth situation and
so you may want to use email for direct exchange rather than posting numerous
download links 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.

If this is a repetitive, dedicated task then we can automate it so VBA does all
the work via menus the user interacts with. IOW, an Excel-based application
that runs as an Addin.

The 'summary' template will contain formulas as well as constants, all inserted
with VBA.

FYI:
Visual Basic IS a real programming language, BTW! Apps that support VBA merely
exposed their ObjectModel to it. Macro is just another name for program
procedures, but VBA programs support all that VB includes. The last version of
VB was 6.0, and is what VBA was/is based on up to MS Office2007. As of MSO2010,
VBA7 is used in both 32bit and 64bit editions.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 2/18/2018 5:56 PM, GS wrote:
....

What is your idea of how to "collaborate" here?

I would need a workbook from you with sample (dummy) of every
conceivable type of input (source) data the project may encounter on 1
sheet, and a sample consolidate sheet to build the Summary template.
From there I'll mock up an app and send it for your review/feedback;
-this will be a back&forth situation and so you may want to use email
for direct exchange rather than posting numerous download links 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.

If this is a repetitive, dedicated task then we can automate it so VBA
does all the work via menus the user interacts with. IOW, an Excel-based
application that runs as an Addin.

The 'summary' template will contain formulas as well as constants, all
inserted with VBA.

FYI:
Visual Basic IS a real programming language, BTW! Apps that support VBA
merely exposed their ObjectModel to it. Macro is just another name for
program procedures, but VBA programs support all that VB includes. The
last version of VB was 6.0, and is what VBA was/is based on up to MS
Office2007. As of MSO2010, VBA7 is used in both 32bit and 64bit editions.

The "real language" was intended mostly as a gibe at MS on the
cmoplexity; I am quite aware of VB as the language; we did a line of
online coal analyzers using first PB7 then VB as the first step to the
PC away from the earlier dedicated HP hardware. So it's not VB itself
that's the problem; it's the overhead of learning enough of the Excel
object model that's the time-killer plus just unfamiliarity in working
with the spreadsheet paradigm.

We had a sidebar conversation a couple weeks ago on using MATLAB for
data analysis being so much simpler owing to its array syntax; similar
holds true for this task in that it would be probably <10 lines of
MATLAB code on the data to arrange it as want and rewrite into the other
sheet. I've not explored the packaging of MATLAB function as an
external app altho it appears the facility is in the base product I have
the license for...I might just give that a go as an experiment.

For your use I'll have to work on removing personal information; don't
think you would need more than just a trivial dataset to illustrate the
idea; I'm sure with a rudimentary pattern of how a "real Excel
programmer" would do it I'd be able to extend it to suit.

--

For your use I'll have to work on removing personal information; don't think
you would need more than just a trivial dataset to illustrate the idea; I'm
sure with a rudimentary pattern of how a "real Excel programmer" would do it
I'd be able to extend it to suit.

Something similar to the example data you posted is fine. I just need to see
how that data is structured for the code logic.

Just so you know, I will use arrays to manage things as I'm not a fan of doing
worksheet read/writes due to the associated slowness.

How does the source data get into its worksheet?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 2/18/2018 11:13 PM, GS wrote:
For your use I'll have to work on removing personal information; don't
think you would need more than just a trivial dataset to illustrate
the idea; I'm sure with a rudimentary pattern of how a "real Excel
programmer" would do it I'd be able to extend it to suit.

Something similar to the example data you posted is fine. I just need to
see how that data is structured for the code logic.

Just so you know, I will use arrays to manage things as I'm not a fan of
doing worksheet read/writes due to the associated slowness.

How does the source data get into its worksheet?

At the moment the new year data still gets pasted into a new copy
manually for the spending calculation; then the rest is entered by hand
throughout the course of the year as described earlier.

The one-time new year isn't such a big deal; it's the revisiting for the
allocation and posting over the year that's the time-killer the new page
would alleviate a fair fraction of owing to the duplication/divisions in
how it is currently structured.

Again, just so _you_ know :) I'm not so much advocating reading/writing
the data; it's just that I can do what I need to get done that way much
more rapidly than the time it's taken to figure out the internals of
Excel to get something done that way...particularly when the data has
been scattered over a dozen sheets in a workbook for each year and I'm
looking at twenty years of financials...this isn't that much of an issue
in that it is just taking what's in one huge sheet at the moment and
turning it into two somewhat smaller ones but all the data is in the one
workbook.

I'll try to make a realistic sample case that isn't huge but at least
representative in the morning...

--

On 2/18/2018 11:13 PM, GS wrote:
....

Just so you know, I will use arrays to manage things as I'm not a fan of
doing worksheet read/writes due to the associated slowness.

Just been working on generalizing some of the previously by-hand fixups

Surely there's a better syntax than

=OFFSET(INDIRECT(ADDRESS(ROW(CompanionFunds),COLUM N(CompanionFunds))),MATCH(V79,CompanionFunds,0)-1,-5)

to get a particular value from the companion fund data
area...CompanionFunds is the name array of FundNames including all the
other pools than Pool 1; Column V is the fund name for the given Pool 1
fund. The column offset -5 happens to be where the HistoricalValue is
located; purpose here is to be able to add that to the HV in the given
fund -- oh, while I write this it dawns on me that SUMIF() may come to
the rescue here as well...

Still a ? ran across that hadn't thought of previously; in MATLAB if
there is more than one match, I get the list of all back automagically;
in Excel MATCH and friends stop after first...what's the way to find all
matches?

--

On 2/18/2018 5:56 PM, GS wrote:
....

-this will be a back&forth situation and so you may want to use email
for direct exchange rather than posting numerous download links here.
....

I'm working on cleaning up a version this AM...I've got until noon
budget meeting -- if you visit before then, send me an e-mail at the
result of

=CHAR({100,112,98,111,122,97,114,116,104,64,115,11 9,107,111,46,110,101,116})

if CHAR would operate on the array instead of just the first element in
the array (I'm sure there's a way to do this in Excel it would just be

CHAR([100 112 98 111 122 97 114 116 104 64 115 119 107 111 46 110 101 116])

in Matlab :)

--

On 2/19/2018 9:51 AM, dpb wrote:
....

CHAR([100 112 98 111 122 97 114 116 104 64 115 119 107 111 46 110 101 116])

in Matlab :)


Actually

char([100 112 98 111 122 97 114 116 104 64 115 119 107 111 46 110 101 116])

<VBG

--

On 2/18/2018 5:56 PM, GS wrote:
...

-this will be a back&forth situation and so you may want to use email for
direct exchange rather than posting numerous download links here.
...

I'm working on cleaning up a version this AM...I've got until noon budget
meeting -- if you visit before then, send me an e-mail at the result of

=CHAR({100,112,98,111,122,97,114,116,104,64,115,11 9,107,111,46,110,101,116})

if CHAR would operate on the array instead of just the first element in the
array (I'm sure there's a way to do this in Excel it would just be

CHAR([100 112 98 111 122 97 114 116 104 64 115 119 107 111 46 110 101 116])

in Matlab :)

Square brackets in VBA denotes a range. The following are equivalent:

Range("A1:E1")
[A1:E1]

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Mon, 19 Feb 2018 13:56:05 -0500 schrieb GS:

=CHAR({100,112,98,111,122,97,114,116,104,64,115,11 9,107,111,46,110,101,116})

CHAR([100 112 98 111 122 97 114 116 104 64 115 119 107 111 46 110 101 116])

Square brackets in VBA denotes a range. The following are equivalent:

Range("A1:E1")
[A1:E1]

try:
CHAR({100;112;98;111;122;97;114;116;104;64;115;119 ;107;111;46;110;101;116})
with CONCAT as array formula.



Regards
Claus B.
--
Windows10
Office 2016