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: 30
  • Raw Data.png
    Raw Data.png
    69.3 KB · Views: 28

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
or this

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

Having IDs and names in the same cell won't work either. The real data has first and last names. Some departments have a dozen or so members. and others may have over 100 members. To keep things simple, I've also left out a column that provides any text messages included with the response.
 
Upvote 0
you said "in single row" and no "unconfirmed", so?
To clarify, no "unconfirmed" connected so someone that has actually confirmed one response. From the report perspective, each person either confirmed or they are unconfirmed. I don't want the report to say John smith is confirmed, and directly under that row is a row that is entirely empty except for Unconfirmed (because John smith didn't confirm every message he received. Message are sent through phone, email, SMS. One message is sent than a few minutes later a second message is sent if the previous one was not confirmed. So many people that confirm, will also have some messages that they did not confirm. I want to display when someone confirms, but I don't want to display uncofirmed for the messages that they were unable to respond to.
 
Upvote 0
Does this look correct to you?

Book5
ABCD
1DATA.DeptDATA.NameIDConfirmed
23850MATTHEW69421Confirmed
33878GLENN27830Not Confirmed
43878THOMAS27699Confirmed
53878WILLIAM4078Confirmed
63932BAYARD71840Confirmed
73932CARL72565Confirmed
83932JAMES30442Confirmed
94170RAPHAEL72733Not Confirmed
104170WILLIAM71729Confirmed
Sheet3


Here is the Mcode from Power Query if you concur with the solution.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"})
in
    #"Removed Columns"
 
Upvote 0
Does this look correct to you?

Book5
ABCD
1DATA.DeptDATA.NameIDConfirmed
23850MATTHEW69421Confirmed
33878GLENN27830Not Confirmed
43878THOMAS27699Confirmed
53878WILLIAM4078Confirmed
63932BAYARD71840Confirmed
73932CARL72565Confirmed
83932JAMES30442Confirmed
94170RAPHAEL72733Not Confirmed
104170WILLIAM71729Confirmed
Sheet3


Here is the Mcode from Power Query if you concur with the solution.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"})
in
    #"Removed Columns"

Yes that looks good! The should be before the name, but otherwise that looks good. I've recently begun working with Power Query, and are not too familiar with Mcode. But I believe it can review the code to figure out how to adapt it to what I have. I'll work with this a bit over the next day or so to see how it works.

Thank you for your help!
 
Upvote 0
Moved the column.

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}, {"Call Result", type text}, {"Attempt", Int64.Type}, {"Delivery Method", type text}, {"Confirmed?", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"DATA", each _, type table [Dept=number, ID=number, Name=text, Call Result=text, Attempt=number, Delivery Method=text, #"Confirmed?"=text]}}),
    #"Expanded DATA" = Table.ExpandTableColumn(#"Grouped Rows", "DATA", {"Dept", "Name", "Confirmed?"}, {"DATA.Dept", "DATA.Name", "DATA.Confirmed?"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded DATA", {{"ID", type text}}, "en-US"),{"ID", "DATA.Confirmed?"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns", {"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "ID"}, {"Merged.2", "Confirm"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Confirm]), "Confirm", "ID"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "ID", each if[Y]<> null then [Y] else [N]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Confirmed", each if [Y] <> null then "Confirmed" else "Not Confirmed"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Y", "N"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DATA.Dept", "ID", "DATA.Name", "Confirmed"})
in
    #"Reordered Columns"
Book51.xlsx
ABCD
1DATA.DeptIDDATA.NameConfirmed
2385069421MATTHEWConfirmed
3387827830GLENNNot Confirmed
4387827699THOMASConfirmed
538784078WILLIAMConfirmed
6393271840BAYARDConfirmed
7393272565CARLConfirmed
8393230442JAMESConfirmed
9417072733RAPHAELNot Confirmed
10417071729WILLIAMConfirmed
Sheet2
 
Upvote 0
some other ways are a query (table) or a basic pivot table (don't need power query) with dataset defined by

Code:
SELECT DISTINCT A.Dept, A.ID, A.Name, 'Confirmed' AS [Confirmed]
FROM Data A
WHERE A.[Confirmed] = 'Y'
UNION
SELECT DISTINCT B.Dept, B.ID, B.Name, 'Unconfirmed'
FROM Data B
WHERE B.ID NOT IN (
SELECT DISTINCT C.ID
FROM Data C
WHERE C.[Confirmed] = 'Y')

PS. I started with renaming the [Confirmed?] field to [Confirmed] as the "?" was a problem for the query.
 
Last edited:
Upvote 0
some other ways are a query (table) or a basic pivot table (don't need power query) with dataset defined by

Code:
SELECT DISTINCT A.Dept, A.ID, A.Name, 'Confirmed' AS [Confirmed]
FROM Data A
WHERE A.[Confirmed] = 'Y'
UNION
SELECT DISTINCT B.Dept, B.ID, B.Name, 'Unconfirmed'
FROM Data B
WHERE B.ID NOT IN (
SELECT DISTINCT C.ID
FROM Data C
WHERE C.[Confirmed] = 'Y')

PS. I started with renaming the [Confirmed?] field to [Confirmed] as the "?" was a problem for the query.

Thank you Fazza for the idea. I haven't yet had time to try it but I will to see how it works. Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top