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