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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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,499
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,263
Messages
5,635,145
Members
416,844
Latest member
ryanangus496

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