Solved

# Excel Pivot Table - providing sum

Posted on 2011-10-18
262 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
Question by:COBOLforever
16 Comments

LVL 39

Expert Comment

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
``````
0

LVL 50

Expert Comment

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

Use SUMIF...
SUMIF.xlsx
0

Author Comment

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

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

working with the macro now...
0

Author Comment

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

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

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 41

Accepted Solution

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 41

Expert Comment

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

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

going to bed...
0

LVL 41

Expert Comment

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

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`
``````

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

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you toâ€¦
Overview: This article:       (a) explains one principle method to cross-reference invoice items in QuickbooksÂ®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a Mâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!