Pivot Table to Report Confirmation (or not) Status

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hey Everyone, thanks for looking at this post. Hopefully the details provide a clear description of what I need.

Each month I create a spreadsheet report that provides details on responses from about 2000 people to indicate whether or not they confirmed receipt of a monthly system generated message. This report is then sent to a leadership team for review. The report is a pivot table with Department #, ID, Name and if they confirmed message receipt or not. This report also includes some slicers, charts and basic statistical information. I created the report a few years ago, and are now revisiting it to see if I can find a simpler way to indicate the confirmation status, and to provide some additional detail. I suspect the solution is somewhat simple, but I've been working with the current report for so long I'm not able to see alternatives.

My challenge is that notifications are sent to each person through various channels (phone, email, SMS, etc.). The raw data I receive includes multiple rows for each person - one row for each channel the message was sent. If someone confirms receipt through SMS, but they also received an email and a phone call, the raw data has three rows of data for that one person. Only one row will ever show that they confirmed, the other two rows will show they did not confirm through those channels. My goal is to display each person's name (and some other data) on a single row and in the last column indicate if they are Confirmed or Unconfirmed.

My current pivot table solution (tabular layout) has Department, ID, Name in rows and Confirmation Response (Y or N) in columns. The Confirmation Response field is added to the Values field in the PT and counts how many times they did, or did not confirm. They can confirm only once, but each communications method that is not confirmed is also counted, as unconfirmed. Conditional formatting changes the row for each unconfirmed person RED, so they are easily identified from those that did confirm (black text). When someone does not confirm at all, the Y cell for that record is blank, and triggers the conditional formatting. The Y and N columns are hidden, to keep the report less cluttered for viewers.

I've attached a two screen shots to provide visuals. The RAW DATA screenshot shows a small sample of the data I receive. The REPORT screenshot shows a mocked up sample of the Current Report, as well as the Desired Report
 

Attachments

  • Pivot Table Report.png
    Pivot Table Report.png
    78.4 KB · Views: 10
  • Raw Data.png
    Raw Data.png
    69.3 KB · Views: 10

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,918
Office Version
  1. 2019
Platform
  1. Windows
Cannot manipulate data from a picture. Please upload your sample data employing XL2BB function. See my signature block for link to instructions.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,052
based on your RAW DATA
DeptIDNameConfirmed
385069421MATTHEWConfirmed
Unconfirmed
38784078WILLIAMConfirmed
27699THOMASConfirmed
27830GLENNUnconfirmed
393230442JAMESConfirmed
Unconfirmed
71840BAYARDConfirmed
Unconfirmed
72565CARLConfirmed
Unconfirmed
417071729WILLIAMConfirmed
Unconfirmed
72733RAPHAELUnconfirmed
 

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Here is a copy of my test data

Book1
BCDEFGH
8DeptIDNameCall ResultAttemptDelivery MethodConfirmed?
938784078WILLIAMConfirmed1SMSY
10387827699THOMASConfirmed1SMSY
11387827830GLENNSent1SMSN
12387827830GLENNSent2Personal EmailN
13387827830GLENNSent3EmailN
14387827830GLENNTo Voicemail4Cell PhoneN
15393230442JAMESTo Handset1SMSN
16393230442JAMESConfirmed2Personal EmailY
17393230442JAMESRecipient Hung Up3Cell PhoneN
18393230442JAMESDownstream Communication Error4Home PhoneN
19385069421MATTHEWConfirmed1SMSY
20385069421MATTHEWSent2Personal EmailN
21385069421MATTHEWTo Voicemail3Cell PhoneN
22417071729WILLIAMSent1Personal EmailN
23417071729WILLIAMConfirmed2Home PhoneY
24393271840BAYARDConfirmed1SMSY
25393271840BAYARDSent2Personal EmailN
26393271840BAYARDTo Voicemail3Cell PhoneN
27393272565CARLSent1Personal EmailN
28393272565CARLTo Voicemail2Home PhoneN
29393272565CARLSent1Personal EmailN
30393272565CARLConfirmed2Home PhoneY
31417072733RAPHAELCarrier Expired1SMSN
32417072733RAPHAELSent2Personal EmailN
33417072733RAPHAELOut of Service3Cell PhoneN
34417072733RAPHAELTo Voicemail4Home PhoneN
35417072733RAPHAELCarrier Expired1SMSN
36417072733RAPHAELSent2Personal EmailN
37417072733RAPHAELOut of Service3Cell PhoneN
38417072733RAPHAELTo Voicemail4Home PhoneN
39417072733RAPHAELCarrier Expired1SMSN
40417072733RAPHAELSent2Personal EmailN
41417072733RAPHAELOut of Service3Cell PhoneN
Sheet4
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,052

ADVERTISEMENT

post#3 is this what you want? (Pivot Table)
 

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
based on your RAW DATA
DeptIDNameConfirmed
385069421MATTHEWConfirmed
Unconfirmed
38784078WILLIAMConfirmed
27699THOMASConfirmed
27830GLENNUnconfirmed
393230442JAMESConfirmed
Unconfirmed
71840BAYARDConfirmed
Unconfirmed
72565CARLConfirmed
Unconfirmed
417071729WILLIAMConfirmed
Unconfirmed
72733RAPHAELUnconfirmed

Yes, that's one way to present the data, but the confirmation status is not consistant. When someone confirms with the very first response they have a single value displayed "Confirmed". When someone does not confirm to any responses they have a single response "Unconfirmed". But when someone receives several messages, and eventually responds to one of them they will have two vales displayed "Confirmed" and "Unconfirmed" (for the messages with which they did not respond). So for the end user reviews the report they are going to ask if those people are confirmed or not. That's my dilemma.
 

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes, that's one way to present the data, but the confirmation status is not consistant. When someone confirms with the very first response they have a single value displayed "Confirmed". When someone does not confirm to any responses they have a single response "Unconfirmed". But when someone receives several messages, and eventually responds to one of them they will have two vales displayed "Confirmed" and "Unconfirmed" (for the messages with which they did not respond). So for the end user reviews the report they are going to ask if those people are confirmed or not. That's my dilemma.


To add one more thought. In the example you provided, if there were a way to surpress, hide or not show the row that has Unconfirmed and no additional information in the same row, that would be ideal. I'm looking for a single row, with Dept, ID, Name and their confirmation status, confirmed, or unconfirmed. All in one row. Perhaps a pivot table isn't the best solution.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,052
like this?
DeptIDNameConfirmed
385069421MATTHEWConfirmed
38784078WILLIAMConfirmed
27699THOMASConfirmed
393230442JAMESConfirmed
71840BAYARDConfirmed
72565CARLConfirmed
417071729WILLIAMConfirmed
 

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
like this?
DeptIDNameConfirmed
385069421MATTHEWConfirmed
38784078WILLIAMConfirmed
27699THOMASConfirmed
393230442JAMESConfirmed
71840BAYARDConfirmed
72565CARLConfirmed
417071729WILLIAMConfirmed
Actually like the example below. This data is copied and pasted into the form I'm looking for. One row for each person, and Confirmed or Unconfirmed to indicate if they responded.

Book1
HIJK
8DeptIDNameStatus
9385069421MATTHEWConfirmed
1038784078WILLIAMConfirmed
1127699THOMASConfirmed
1227830GLENNUnconfirmed
13393230442JAMESConfirmed
1471840BAYARDConfirmed
1572565CARLConfirmed
16417071729WILLIAMConfirmed
1772733RAPHAELUnconfirmed
Sheet5
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,052
or this

DeptIDNameConfirmed
38784078, 27699WILLIAM, THOMASConfirmed
393230442, 71840, 72565JAMES, BAYARD, CARLConfirmed
385069421MATTHEWConfirmed
417071729WILLIAMConfirmed
 

Watch MrExcel Video

Forum statistics

Threads
1,111,854
Messages
5,541,460
Members
410,546
Latest member
htran4
Top