Summarize Counts in an Adjoining Column - Excel

[Scott_AZ]

Hello, 

Thank you if you're reading this.   I have a number of users in a column (array) called EMail.  Sometimes they are in the column multiple times.  I need to count the total occurrences for each user and put it in the adjoining column.

As an example, in the data below, Deb Ruiz appears 5 times.  Megan strand occurs 4 times. I need to keep the Email (User) and the count associated with the manager's name to the right.  Ultimately, I will email the manager and tell them that "this happened 5 times with user Deb Ruiz" and "this happened 4 times with Megan Strand etc...   I have about 700 total user instances in the Email row / array.  What formula will work?

Can anyone help me please?

Scott

Email	Count	Manager Name
Deb Ruiz @ email		Alison Travis
Deb Ruiz @ email		Alison Travis
Deb Ruiz @ email		Alison Travis
Deb Ruiz @ email		Alison Travis
Deb Ruiz @ email	5	Alison Travis
Megan Strand @ email		Alison Travis
Megan Strand @ email		Alison Travis
Megan Strand @ email	4	Alison Travis
Megan Strand @ email		Alison Travis
Josh Allen @ email	2	Alison Travis
Josh Allen @ email		Alison Travis
Lilly White @ email	1	Andrea Villarreal
George Jetson @ email		Andrea Villarreal
George Jetson @ email	4	Andrea Villarreal
George Jetson @ email		Andrea Villarreal
George Jetson @ email		Andrea Villarreal

[#2 · Accepted solution]

@Scott_AZ 

Easy with a pivot table.

Select any cell within your table and choose Insert, Pivot table
Drag both Email and Manager name fields to the row area. The drag either of the two to the bottom-right area. See screen-shot:

[#3]

Jan - thank you so much for the reply.....  I did these steps as that is all that I could see to do....

Almost works but I am not getting the Count it appears >>

Just below Column heading "Count" I chose Insert and pivot table
Selected Table / Range is --- '90 Day all_phishing_activity_08'!$A$1:$I$656
Choose where you want the Pivot Table Report to be placed - Chose Existing Worksheet----- '90 Day all_phishing_activity_08'!$B$4

[#4]

Actually I think I got it.... I had to drag the Count field down to the Values Box
THANKS JAN !!
PS - Any relation to Sasha Pietrse ?

[#5]

@Scott_AZ 

Something similar with a DA formula and a bit of conditional formatting

= LET(
    distinctPairing, UNIQUE(Table1),
    distinctEMail,   TAKE(distinctPairing,,1),
    distinctManager, TAKE(distinctPairing,,-1),
    occurences,      COUNTIFS(Table1[Email],distinctEMail),
    resultList,      HSTACK(distinctManager, TEXTBEFORE(distinctEMail,"@"), occurences),
    SORT(resultList)
  )

[#6]

@Peter Bartholomew 

Hi Peter !   Wow, you really have a solid grasp of Excel formula concepts.   Thank you very much for your time in presenting this solution.   Ever appreciated. I may just give it a gander.

Respectfully,  Scott

[#7]

@Scott_AZ 

If you have access to a 365 license this should at least prove the spreadsheet is real and not an image drawn from a fevered imagination!

[#8]

Very fancy Mr. Guru !
Many thanks for your insights
Scott