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.

--