Hi, I'm not an Excel guru, so hoping to get some insight, tips, or guidance on how to approach a fairly complex issue.

We receive a list of stores from a client that we need to turn into a master email list we'll use for doing email marketing. The list we receive is based around single unique Store Numbers for each record (row). But we need to convert this to being based around a single unique Email Address for each record. There are over 1,000 records in the file we receive.

The problem is that each of their records will have multiple staff members with email addresses included (a Main, Sales Manager, and Owner). So these need to be extrapolated into individual records.

BUT, there are also instances where the same email address is used on multiple records (e.g, a store has a few locations with the same staff member). And in these instances where a single staff member (email address) is included on multiple store records, we would still like to know what all Store Numbers they are associated with. So we may need to combine the individual store numbers into a single field, probably comma separated.

And lastly, we would like some sort of indication of what role they have (e.g., Main, Sales, Owner). A simple 'X' in a corresponding column would be fine for this.

To make it more clear here is a VERY simplified version of this. The first sheet (Original) is basically what we receive from the client. The second sheet (Final Result) shows how the first couple records should be refactored (I only did a couple for this).

Sample-Store-List.xlsx

ANY guidance or direction on how to approach this would be most welcomed!