Excel: Comparing values between 2 Pivot tables and calculating values where the name is matching

Question

Dear Community,

I would really appreciate it if you could help me with the following task. (A bit complex but interesting)

The Task: create a formula that compares February vs January data in 2 pivot tables by:

1. firstly, check if Company Name matches in both columns, for example, Company A in B column and Company A in the E column.
2. Secondly, if it matches, it checks the countries INSIDE the Company ONLY. If the country matches, the formula should be the following: Sum of Amount for Specific country in February - Sum of Amount for specific country in January, for example:

Company A has Germany both in January and February, therefore, in Column H it should calculate 80 - 17 = 63

• if the Company name appears in both columns, for example, Company A - then write in H column YES
• if Company is not in another column, write - no value in column H
• If there is no exact country in the B column if compared with the F column, write - no value. The formula should check the country inside a specific Company ONLY.
• Data should be compared by looking at FEBRUARY DATA, Column F

Many thanks in advance! :)

Answer 1

Hi,

Why don't you build a single Pivot Table with columns for January and February?