Sort or List responses with duplicates

[Greg Stough]

Okay, here's my problem. An automatic notification system attempts to sequentially contact over 2000 employees through each contact method that they have listed. This could be phone, cell phone, email, SMS, etc. When the first contact method is finished it lists "yes" or "no" to indicate that the employee has been contacted. If the first contact method was successful, no additional contacts are made to this employee. If the contact method was unsuccessful on the first try, the system tries with the second contact method. This repeats until the employee acknowledges they have received the message, or, all contact methods have been attempted.

I need to identify and report on who has been successfully been contacted, and also who has not been contacted. Identifying those successfully contacted is easy. I filter the Confirmed column to display "Yes" responses, and I have my list. The problem is that I can't easily determine exactly who has not been contacted by any means. If I filter the Confirmed column to display "No" responses the results will display every failed attempt, even if the person responded "Yes" with a different contact method. I only want to list people that have not been contacted by any method at all.

It seems like this should be easy to figure out, but I can't figure it out. I can run several different reports from the notification system to list only those that confirmed, and another for only those that did not confirm, but it's a bit cumbersome. I'd like to run a single report that displays everyone, and then use a formula, pivot table or something, to list only those that have confirmed "Yes", and a separate display for those that have not been confirmed at all "no". This data is then dropped onto separate sheets in a template for some additional reporting with slicers and pivot tables and sent to leadership for their review.

Below is a sample report.

[TABLE="class: grid, width: 398"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Column1[/TD]
[TD]ID #[/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD]Abraham[/TD]
[TD]Thatcher [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]18000[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Abraham[/TD]
[TD]Thatcher [/TD]
[TD]SMS[/TD]
[TD="align: right"]18000[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Abraham[/TD]
[TD]Thatcher [/TD]
[TD]Email[/TD]
[TD="align: right"]18000[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Teresa [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]11140[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Teresa [/TD]
[TD]SMS[/TD]
[TD="align: right"]11140[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Teresa [/TD]
[TD]Email[/TD]
[TD="align: right"]11140[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Teresa [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]11140[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]Orwell [/TD]
[TD]SMS[/TD]
[TD="align: right"]19799[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]Monroe [/TD]
[TD]Email[/TD]
[TD="align: right"]10028[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Charles[/TD]
[TD]Monroe [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]10028[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Christopher[/TD]
[TD]Mandela [/TD]
[TD]SMS[/TD]
[TD="align: right"]12969[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Christopher[/TD]
[TD]Mandela [/TD]
[TD]Email[/TD]
[TD="align: right"]12969[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Christopher[/TD]
[TD]Mandela [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]12969[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Lincoln [/TD]
[TD]SMS[/TD]
[TD="align: right"]12213[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Lincoln [/TD]
[TD]Email[/TD]
[TD="align: right"]12213[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]King [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]10679[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]King [/TD]
[TD]SMS[/TD]
[TD="align: right"]10679[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mahatma[/TD]
[TD]Kennedy [/TD]
[TD]Email[/TD]
[TD="align: right"]13444[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Mahatma[/TD]
[TD]Kennedy [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]13444[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mahatma[/TD]
[TD]Kennedy [/TD]
[TD]SMS[/TD]
[TD="align: right"]13444[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mahatma[/TD]
[TD]Kennedy [/TD]
[TD]Email[/TD]
[TD="align: right"]13444[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Margaret[/TD]
[TD]Gates [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]15585[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Margaret[/TD]
[TD]Gates [/TD]
[TD]SMS[/TD]
[TD="align: right"]15585[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Margaret[/TD]
[TD]Gates [/TD]
[TD]Email[/TD]
[TD="align: right"]15585[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Marilyn[/TD]
[TD]Gandhi [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]14042[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Marilyn[/TD]
[TD]Gandhi [/TD]
[TD]SMS[/TD]
[TD="align: right"]14042[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Marilyn[/TD]
[TD]Gandhi [/TD]
[TD]Email[/TD]
[TD="align: right"]14042[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Marilyn[/TD]
[TD]Gandhi [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]14042[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Martin[/TD]
[TD]de Gaulle [/TD]
[TD]SMS[/TD]
[TD="align: right"]13507[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Mother[/TD]
[TD]Darwin [/TD]
[TD]Email[/TD]
[TD="align: right"]12619[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mother[/TD]
[TD]Darwin [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]12619[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mother[/TD]
[TD]Darwin [/TD]
[TD]SMS[/TD]
[TD="align: right"]12619[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Muhammad[/TD]
[TD]Columbus [/TD]
[TD]Email[/TD]
[TD="align: right"]18826[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Muhammad[/TD]
[TD]Columbus [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]18826[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Nelson[/TD]
[TD]Churchill [/TD]
[TD]SMS[/TD]
[TD="align: right"]13545[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Nelson[/TD]
[TD]Churchill [/TD]
[TD]Email[/TD]
[TD="align: right"]13545[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Nelson[/TD]
[TD]Churchill [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]13545[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Nelson[/TD]
[TD]Churchill [/TD]
[TD]SMS[/TD]
[TD="align: right"]13545[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Winston[/TD]
[TD]Ali [/TD]
[TD]Email[/TD]
[TD="align: right"]17073[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Winston[/TD]
[TD]Ali [/TD]
[TD]Phone 1[/TD]
[TD="align: right"]17073[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Winston[/TD]
[TD]Ali [/TD]
[TD]SMS[/TD]
[TD="align: right"]17073[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[frank_AL]

Greg,

I can provide you a solution via VBA. May take a few days as we are traveling with 3 granddaughters this week.

Let me know if you want me to proceed.

 

[Trebor76]

Hi Greg,

Assuming (based on you original post) your data range is A1:E42 with ID in Col. D and 'yes' 'no' in Col. E, you could put this formula into cell F2...

=IF(COUNTIFS($D$2:$D$42,D2,$E$2:$E$42,"No")=COUNTIF($D$2:$D$42,D2),"Not Contacted","Contacted")

...and then fill it down to F42 and use that range to filter those staff that have been "Not Contacted".

Regards,

Robert

 

[Greg Stough]

Thanks for the offer Frank. For the moment let's hold off on a VBA solution. Since copies of the final workbook results are sent out to over 150 people for review each month (many of which have very little experience with Excel) I believe my phone and email would blow up with concerns about the extra pop-message that would display when they open the spreadsheet. I will keep your offer in mind though, should I not be able to come up with any formula-based solutions. Thank you for your reply!

 

[Greg Stough]

Robert, thank you for your help.

I believe that formula will work as I need it. It will take a few days until I have time to do some testing on it, but so far that looks good.

Thank you very much!

- Greg

 

[Trebor76]

Hi Greg,

Thanks for letting us know and you're welcome.

Hi frank_AL,

Thanks for the Like

Robert