Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Pivot Table - providing sum

Posted on 2011-10-18
16
Medium Priority
?
318 Views
Last Modified: 2012-05-12
Hi EE'rs,

Hope you can help. I am open to using either Excel or Access but prefer Excel

Simple task but need better way to do this.

Example Data that is currently in spreadsheet
KEY           Date       Code1    Code2       Amount
12345        Feb10      A            B              20.00
12345        Aug20      A           C               10.00
12345        Aug30      A            G              30.00
12222        Feb20       C           A               20.00
12222        Mar20       C            B               55.00
12222        Jul10         B           C                40.00

I need to end up with        
12345        dontcare  dontcare    dontcare    60.00
12222        dontcare  dontcare    dontcare    105.00
In the dontcare columns I am saying that I dontcare which row the value is kept from - and it can be random, it doesn't matter.

I that stated clear enough?  I think I can use Pivot Table to do this but can't quite get it.

Thanks.
0
Comment
Question by:COBOLforever
  • 8
  • 3
  • 3
  • +2
16 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 36989760
You can run this macro on your data and it should do it. A pivot table will calculate your total but will not easily give you any value for columns B through D.

Thomas
Sub ConsolidateRows()
'takes rows and consolidate one or many cells, based on one or many cells matching with above or below rows.

Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant

'**********PARAMETERS TO UPDATE****************
Const strMatch As String = "A"    'columns that need to match for consolidation, separated by commas
Const strConcat As String = "E"     'columns that need consolidating, separated by commas
'columns that are neither in strMatch or strConcat will be ignored, only the first row values will remain

Const strSep As String = "+"     'string that will separate the consolidated values
Const bNum As Boolean = True 'True if you want to consolidate numbers in a formula
'*************END PARAMETERS*******************

application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes

colMatch = Split(strMatch, ",")
colConcat = Split(strConcat, ",")

lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row

For j = 0 To UBound(colMatch)
    Cells(1, 1).CurrentRegion.Sort Key1:=Cells(1, colMatch(j)), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                    :=xlSortNormal
Next


For i = lastRow To 2 Step -1 'loop from last Row to one
    
    For j = 0 To UBound(colMatch)
        If Cells(i, colMatch(j)) <> Cells(i - 1, colMatch(j)) Then GoTo nxti
    Next
    
    
    For j = 0 To UBound(colConcat)
        Cells(i - 1, colConcat(j)) = IIf(bNum, "=", "") & Cells(i - 1, colConcat(j)) & strSep & _
                                        IIf(bNum, Replace(Cells(i, colConcat(j)), "=", ""), Cells(i, colConcat(j)))
    Next
    
    Rows(i).Delete
    
nxti:
Next

application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36989765
You could use a formula in the next column to put the sum against the last entry for each KEY, e.g. in F2

=IF(A2=A3,"",SUMIF(A$2:A2,A2,E$2:E2))

copy down column

Now filter by column F to display non-blanks

regards, barry
0
 
LVL 10

Expert Comment

by:Tony Barkdull
ID: 36989776
Use SUMIF...
 SUMIF.xlsx
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:COBOLforever
ID: 36989930
Using my life data and in the last column the formula:=IF(A2=A3,"",SUMIF(A$2:A2,A2,E$2:E2)

108351581109000008602001      112      915      0422900      -422900      -422900
108351581109000008602004      112      916      0597000      -597000      -597000
108351581112000008602001      113      915      0000000      0      
108351581112000008602001      113      915      0422900      -422900      -1019900
108351581112000008602004      113      916      0000000      0      
108351581112000008602004      113      916      0597000      -597000      -859200

see that the row 1 and 2 and row 3 is ok probably because it is 0 then row 4 is the sum of unequal keys row 1 and 2
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36989979
the sumif can't take that many characters, so it confuses your values and thinks them identical.

My macro seems to work better for that, or you can use
=IF(SUMPRODUCT((A$2:A2=A2)*1)=1,SUMPRODUCT((A:A=A2)*E:E),"")

T
0
 

Author Comment

by:COBOLforever
ID: 36990131
working with the macro now...
0
 

Author Comment

by:COBOLforever
ID: 36990396
Ok! So it works!  Only how do I get it to give me the actual results instead of the formula? I tried setting the format of the column as General but that did not do it.

Also, I had to remove the last line in the macro - the macro was hanging there with a syntax error.
0
 

Author Comment

by:COBOLforever
ID: 36990489
This macro is super cool though. Just that last step.  Also - there are 45k rows - is there anyway to tell how far along it is in the calc. It is using 100% CPU and 8 processors and has been running for about 30 minutes so far.
0
 

Author Comment

by:COBOLforever
ID: 36990497
sorry - - wanted to shout out to barryhoudini, nutsch, and tbarkdull

baryhoudini - went with yours first but couldn't get it to work as per comment above at 8:21pm; I got really close but did not know how to continue.

tbarkdull - tried yours next but it was not totaling in the same row and reducing the rows

Thanks so much though.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 36990563
Pivot Table is your best bet - if you ever have to do this type of exercise more than once, and on such a large dataset, I recommend taking a few minutes with this - you will not regret it.  Load the data, create the table - very straight forward -> realize its very straight forward after having built a couple.  And this is a fairly straight forward example - no time like the present.

Select the data, then Insert Pivot Table.  Select KEY as Row Labels, then the two amounts (SUM option) in the Values section.

See example with your data - taking it the last step, correctly, attached.  Try it with your REAL data.  Let me know.

For future (regardless of how you go with this one) - check out this brief tutorial on Pivot Tables for Excel 2007 http://www.contextures.com/CreatePivotTable.html, for Excel 2003 http://www.techonthenet.com/excel/pivottbls/create.php

Cheers,

Dave
pivotExample.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990597
Just as some incentive - I created a random sample of data about 50,000 rows.

Open this up and refresh the pivottable to see how long it takes to refresh (note random functions in the sheet, so you can see it working...)

I know its 10 MB, but worth the effort for the demonstration.  Hope this helps!

Dave




pivotExample.xls
0
 

Author Comment

by:COBOLforever
ID: 36990700
Hi dlmille: I am familiar somewhat with PT's - and am a big fan. I just could not figure out how to do it and still keep all the rest of the 26 columns of data for each row that I needed. The example I gave was simplistic for brevity.  The real story is somewhat the same except that I have many more columns of data than I was expressing. Also - I think the real kicker is that there are two columns that I need to sum. I think I need more work that I originally thought. Here is the real story if you are interested.
I have data like originally exampled only there are two amounts. The amounts may be different between all instances of the data where the key is the same as shown.

 I think I need to extract all columns + one of the amounts into a new sheet and then do the same with all columns and the second amount field into a separate sheet. Then perform the summing on each such that I end up with a single row where the amount is a sum of all the amounts where the key matched.

Then pull them all back together into a new sheet with the amounts side-by-side once again, but now on a single row for each iteration of the key.  

I absolutely am certain I need to do somethnig like that. However, in the step where I extract them out to separate sheets, where I then need to collapse the rows and sum the amounts, how do I do that with PVT?

When I tried, I ended up with a severely tiered PVT but the columns where I thought I was summing the amount was zero for all rows in the PVT. The way I built it was to place all the columns  I needed in the Row of the PVT and the SUM of the amount in the data in the E-values. I got it to work when I just used the key in the Row and the amount in the E-value but it of course did not have all the other columns of data I needed.
0
 

Author Comment

by:COBOLforever
ID: 36990702
going to bed...
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990797
That's quite a story and I'm glad you're a fan.  Could you upload just a few rows of data (not sensitive/mockup if need be) that have all the columns, then just paint an example of the output.  If it can be done with Pivot, we can help along that approach, while the VBA and formula solutions can work in parallel.

I've mocked up another very close to your original question - maybe it will suffice if you'd explain what the layout needs to look like other than what's already there in the pivot table.  Given your experience in this arena, I'm probably missing something about the layout you need.

Have a good nite.  I'll check on what you have in the AM.

Cheers,

Dave
pivotExample.xls
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 1000 total points
ID: 36991002
Something which might be faster for large datasets could be:

Save your data file (as excel, as csv, or something)
Use Microsoft Query, create a new datasource for your file
Edit the SQL to something like this

SELECT `colToGroupOn`, first(`colToLeaveAlone_1`), first(`colToLeaveAlone_2`), first(`colToLeaveAlone_24`),Sum(`ColToSum`) AS 'Sum of colTosum',Sum(`ColToSum2`) AS 'Sum of colTosum2'
FROM Book9.csv Book9
GROUP BY Book9.`colTogroupOn`

Open in new window


It's fast, relatively painless, and it shouldn't leave your computer hanging for hours. Check the screencast for some steps.

Thomas
nutsch-513427.flv
0
 

Author Closing Comment

by:COBOLforever
ID: 36995331
Thanks for all your help. I did not want to keep the item open for the time it will take me to work both solutions.

Appreciate it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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