Solved

# Merging Pivot Tables

Posted on 2012-08-28
1,236 Views
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
Question by:mscola

LVL 25

Accepted Solution

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

LVL 18

Assisted Solution

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

LVL 25

Expert Comment

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

Author Comment

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

LVL 25

Expert Comment

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

Author Comment

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

LVL 25

Expert Comment

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 Comment

I tried the formula for column H but it doesn't seem to work.
[[#This Row],[FirstOrder]]
Did you mean two square brackets?
0

LVL 25

Expert Comment

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

LVL 18

Expert Comment

0

Author Comment

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

LVL 25

Expert Comment

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

LVL 25

Expert Comment

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 Comment

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

Massimo
0

Author Closing Comment

Thanks a lot for your help !
0

## Featured Post

### Suggested Solutions

excel formula or VBA 7 34
In Excel how to add formula for Quintile? 4 23
Excel formula 5 31
recent documents in word and Excel 5 15
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.