Macro that compares Values in a Column to Based on Values in Different Column

Hi All,

I am working with a large number of client company emails (tens of thousands of emails belonging to hundreds of different client companies.)

I need to determine from the email data the most likely email format for EACH CLIENT.

My spreadsheet has FOUR different columns:

Col A: Full Email Address (ckent@dccomics.com)
Col B: Email Prefix (ckent)
Col C: Email suffix (@dccomics.com)
Col D: Company DC Comics

The Sheet is sorted by Company Name (COL D)

Corporate email formats are actually pretty straightforward. They're usually either:

FirstInitialLastName: (ckent)
FirstNameLastName: (clarkkent)
LastNameFirstInitial: (kentc)
FirstName.LastName: (clark.kent)
FirstName_LastName: (clark_kent)

The Macro needs to do the following:

1) Look at all of the PREFIXES (Col B) for EACH PARTICULAR COMPANY in Col D
2) Determine what format is most common for the company listed in Col D.
3) On Sheet2 List the Email format in COL A and the Company in COL B.

-If the majority of addresses contain NO dots or dashes THEN assume FirstInitialLastName (ie: ckent)
-If the majority of addresses contains DOTS then assume FirstName.LastName (ie: clark.kent)
-If the majority of addresses contains UNDERSCORES then assume FirstName_LastName (clark_kent)

I've included a sample workbook to illustrate.

Thank you,

RJ