Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1350
  • Last Modified:

Merging Pivot Tables

Hello

Is it possible to merge two Pivot Tables?

Here is an example: I've got a table called orders and a table called customers and I'd like to calculate the number of new customers and the total of orders by month/year. I created two Pivot Tables and I was wondering whether it's possible to merge those two tables. Unless .. there is a way to have everything calculated with just one table.
 screengrab
Thanks for your help

Massimo
Book2.xlsx
0
Massimo Scola
Asked:
Massimo Scola
  • 7
  • 6
  • 2
2 Solutions
 
lwadwellCommented:
I merged (hopefully) into one table ... by adding the columns:
G: adding the first order array formula
    ={MIN(IF([Customer]=Orders[[#This Row],[Customer]],[Date]))}
H: changing the count formula to only count if the first order date is the row's date
    =IF(Orders[[#This Row],[First Order]]=Orders[[#This Row],[Date]],COUNTIF([Customer],Orders[[#This Row],[Customer]]),0)


(I am having trouble attaching the updated file)
0
 
krishnakrkcCommented:
Rather than 0, put blank in the Order formula. Otherwise it also count 0 as number.

=IF(Orders[[#This Row],[FirstOrder]]=Orders[[#This Row],[Date]],COUNTIF([Customer],Orders[[#This Row],[Customer]]),"")

In the pivot table Summarize value field (Orders) by 'Count numbers'.

Kris
0
 
lwadwellCommented:
As the pivot would be a SUM of the order counts ... I figured 0 would be fine.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Massimo ScolaAuthor Commented:
lwadwell: Should I put the two pivot tables next to each other?
0
 
lwadwellCommented:
You should only need the one pivot table - once you have all of the data in the one table.
0
 
Massimo ScolaAuthor Commented:
How do I add it to column G?
Like this? I'm sorry if I ask you such questions but PT is rather new for me

screengrab
0
 
lwadwellCommented:
If you have the new columns in the table ... you could 'right click' on the pivot table (anywhere in it) and select 'refresh'.  This should add the new columns to the 'choose fields' in the right hand field list.  You can then drag the new orders column into the Sum Values window on the bottom right to add it to the data area.
0
 
Massimo ScolaAuthor Commented:
I tried the formula for column H but it doesn't seem to work.
[[#This Row],[FirstOrder]]
Did you mean two square brackets?
0
 
lwadwellCommented:
First of an apology to @krishnakrkc ... I should have used "" instead on 0 like suggested as the pivot was a count ... I do know what I was thinking late last night.

Yes ... I did mean the two square brackets the first is related to the table e.g. Orders[] the inner two are [#This Row] is a modifier to indicate only get the values from this row and [FirstOrder] is the column name.
http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

I have attached the spreadsheet this time (EE seems to be behaving this morning).
Book2.xlsx
0
 
krishnakrkcCommented:
@ lwadwell no worries :)
0
 
Massimo ScolaAuthor Commented:
hi thanks for the file
I had a look at the formula in column H and it looks different. Did you change the formula?

Have a look:

formula in my spreadsheet
0
 
lwadwellCommented:
That is not the same as in the spreadsheet I uploaded (I am using Excel 2007 btw) ... I did change the 0 to "".
Formula snap shot
0
 
lwadwellCommented:
Ahh ... an improvement in Excel 2010 ... "The “[#This Row]” notation has been replaced with an “@” notation in formulas".
Ref: http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx
0
 
Massimo ScolaAuthor Commented:
oh that's why the formula looks different on my computer
but thanks - it works!

Massimo
0
 
Massimo ScolaAuthor Commented:
Thanks a lot for your help !
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now