Solved

Sum it Please

Posted on 2013-01-29
12
173 Views
Last Modified: 2013-01-31
Hello All,

I am looking for a VBA solution to do this. I know pivot does this but there are format issues and other things users don’t like. I am staying away from pivot. So what I want to do:
There is a input sheet: where you see that there are groups and underneath those groups, there are parts.
Both the groups and parts have their allocated dollar numbers.
I need a vBA way that dynamically grabs the unqiue values from the table and puts them in a format (look at ‘DesiredSummaryFormat’ sheet
I explained in that sheet how the change (positive / negative)  gets added to the original number as the changed number column

So a dynamic way to create the summary table and then using simple r1c1 or cell formula to drag it across…
Please assist….

Thanks
Book1.xlsx
0
Comment
Question by:Rayne
  • 9
  • 2
12 Comments
 
LVL 24

Assisted Solution

by:Steve
Steve earned 40 total points
ID: 38834185
OK... first using 2007+ Highlight your DataInputTable go to DATA > INSERT > TABLE
This will creat a table to allow for use of table refs in the formula rather than the named ranges.

then use SUMIFS rather than SUMPRODUCT in the desired table.


you say:
GRPAllocated$Changed = GRPAllocated$+changes
wheres changes =GRPAllocated$NEW- GRPAllocated$


this means that
GRPAllocated$Changed  =GRPAllocated$+GRPAllocated$NEW- GRPAllocated$
this means that:
GRPAllocated$Changed  = GRPAllocated$NEW
Is this correct?

Attached file with SUMIFS and TABLE...
Book1.xlsx
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38834809
This will take a little time. I'm working on it.
0
 

Author Comment

by:Rayne
ID: 38836243
Hello All,
Thanks for your reply so far.

The Barman - To indicate and explain clearly, I have attached a file
trackChangeAndSumUp.xlsx
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:Rayne
ID: 38836284
Don't worry about points, I can open a follow up question for further points, I am very generous
Copy-ofUpdated.xlsx
0
 

Author Comment

by:Rayne
ID: 38836285
this is all whats needs to happen
0
 

Author Comment

by:Rayne
ID: 38836308
but dont worry about the registering the row Update to TRUE thing - thats doesn't needs to happen in this question.
0
 

Author Comment

by:Rayne
ID: 38836370
CopyofUpdated explains the source data but summary needs to form out of that data :)
0
 

Author Comment

by:Rayne
ID: 38836372
in a separate sheet
0
 

Author Comment

by:Rayne
ID: 38836506
I guess I can use a pivot, only if users make changes, all they do it refresh the pivot correct - -its that possible from my source data that I explained. Please Assist.
pivotV.xlsx
0
 

Author Comment

by:Rayne
ID: 38836507
please let me know howt o do the pivot in that format
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 460 total points
ID: 38838830
The attached workbook has a macro by the name of SumInputData. You can call it from the Macros menu on the Developer tab or, if you are familiar with VBA, from the VBE window. This macro will convert your data on the DataInputSheet into a report on the sheet DesiredSummaryFormat following your design. Note that this sheet must have one blank report row to begin with. You can endow this row with the cell formats you require and even add formulas which will all be copied to all rows later added to the sheet when the report is prepared.
I am not sure that the two Change columns show the correct result. That is a minor matter. Please look at the available totals being written there. If you wish them to be computed in a different manner that is quite simple. Note also that I omitted the Change group totals intentionally. They can be reinstated if required.
At the top of the code sheet ReportMan you will find a number of constant variables and enums which you can adjust:-
    Const InputSheet As String = "DataInputSheet"
    Const ReportSheet As String = "DesiredSummaryFormat"
    Const TotalDesc As String = "Total"
    
    Private Enum Nin            ' Input sheet
        NinFirstData = 2
        NinGroup = 3            ' columns: 3 = C
        NinPart
        NinDate
        NinGrpA
        NinGrpNew
        NinPrtA
        NinPrtNew
        NinTop                  ' Last column + 1
    End Enum
    
    Private Enum Nrp            ' Report sheet
        NrpFirstData = 5        ' row
        NrpDesc = 2             ' column: 2 = B
        NrpGrpA
        NrpGrpChg
        NrpPrtA
        NrpPrtChg
    End Enum

Open in new window

For example, you can change the names of the worksheets. Just make sure that the name in the code is exactly the same as in the workbook. You can change the first row of data and all the columns for both worksheets. I think the variable names are rather self-explanatory. Just bear in mind that any enum can be assigned an integer in the manner shown. Where no value is assigned the first enum is assigned a zero and all others a value incremented by 1 from the previous enum in the list, whether it has an assigned number or a "natural" one. However, if you need help with any of this do let me know.
130130-Allocation-Report.xlsm
0
 

Author Comment

by:Rayne
ID: 38839874
AWESOME Faustulus
thats works like GOLD
Thank You :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question