Creating a Pivot Table from Two Data Sets

4ever7

New Member
Joined
Jul 22, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Good day.

I'm working on a report that uses information from two data sets, both of which are tabs in my Excel workbook. The first tab is called Failures and it lists the names of the employee name, the failures they received, and the dates when these were evaluated.

SQS_01.png


The second tab contains the evaluation dates and scores received from those evaluations.

SQS_02.png


The report I'm interested at looking at is the failure percentage of the individual. So say, one person had 4 evaluations for the month and had 2 failures, thus the failure rate is 2/4 or 50%. I tried adding the data from the two tabs to the data model, but when I try to connect the data using the employee names I get a message that the selected columns contain duplicate values.

SQS_03.png


Without having to use formulas, is it possible to use pivot tables for this purpose?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

4ever7

New Member
Joined
Jul 22, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
try Alt+D+P
or
Power Query

Hey Sandy! I have the file uploaded here: SQS Sample.xlsx

The pivot table to be made is in the Summary tab. I have used COUNTIF for now, but it will be very challenging if this report is to be sustained using COUNTIF or COUNTIFS because the report is done monthly, and further features such as count of failure types per month, history of failures per agent, etc, will be provided to the recipients on a monthly basis.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
see this one

NameFailuresEvaluationsFailure%NameFailuresEvaluationsFailure %
Clarinda Hinderliter1520%Bernadine Primo1520%
Ginette Quin3560%Cayla Mullinax1520%
Harrison Sine1520%Clarinda Hinderliter1520%
Cayla Mullinax1520%Dagmar Ristau1520%
Maire Laramie2540%Geneva Tant1520%
Bernadine Primo1520%Ginette Quin3560%
Geneva Tant1520%Hannelore Letcher050%
Dagmar Ristau1520%Harrison Sine1520%
Hannelore Letcher050%Inez Bridgers2540%
Noma Austin050%Laci Hollis1520%
Lucas Cypher050%Lucas Cypher050%
Neomi Loud050%Maire Laramie2540%
Laci Hollis1520%Myong Tellez1520%
Myong Tellez1520%Neomi Loud050%
Inez Bridgers2540%Noma Austin050%


I think Power Query will be enough
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,933
Messages
5,834,457
Members
430,287
Latest member
Shinoy98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top