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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.
 

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,918
Office Version
  1. 2019
Platform
  1. Windows
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"
 

Greg Stough

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

ADVERTISEMENT

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!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,918
Office Version
  1. 2019
Platform
  1. Windows
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
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,918
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks for the feedback.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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:

Greg Stough

New Member
Joined
May 26, 2017
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,302
Members
410,545
Latest member
Upsindustrial20
Top