• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1419

# 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.

Massimo
Book2.xlsx
0
Massimo Scola
• 7
• 6
• 2
2 Solutions

Commented:
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

Commented:
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

Commented:
As the pivot would be a SUM of the order counts ... I figured 0 would be fine.
0

Author Commented:
lwadwell: Should I put the two pivot tables next to each other?
0

Commented:
You should only need the one pivot table - once you have all of the data in the one table.
0

Author 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

0

Commented:
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

Author 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

Commented:
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

Commented:
0

Author 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:

0

Commented:
That is not the same as in the spreadsheet I uploaded (I am using Excel 2007 btw) ... I did change the 0 to "".
0

Commented:
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

Author Commented:
oh that's why the formula looks different on my computer
but thanks - it works!

Massimo
0

Author Commented:
Thanks a lot for your help !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.