Need some guidance on how to approach this

Richard2806

New Member
Joined
Oct 28, 2019
Messages
5
Hi Everyone,

I need a little help with something.

I have a report that I get twice a day, on emails that were sent to some of our generic inboxes. Each email has it's own row. Column C of this report contains a list of all the email addresses the email was sent to. There can be one or a number of email addresses in each cell in column C, depending on how many other inboxes the email was sent to, and they are separated by commas. The email may also have been sent to personal or non-relevant inboxes, which I can safely ignore. But this means I can't just search for cells with multiple occurances of the "@" symbol for example, since the email may have been sent to multiple recipients, but only 1 or 2 of the emails are relevant.

So I need to compare those emails addresses against a list of specific email addresses (generic inboxes) in another excel document, where each email address is in an individual cell in column A.

If an email is sent to lets say 2, or 3 of the monitored inboxes, each one will create a job in the system for staff to action, but we really only want one to exist in the system, and that job needs to sit with the correct team for the inquiry. The idea is that I'm comparing where the original email was sent, and if it was sent to more than one inbox, I want to highlight the relevant email addresses, or preferably the entire row in the document so I can follow them up. I'll probably end up hiding all the other rows, to make the report easier to read after it's processed.

The report can be hundreds of lines long, and currently we are going through it manually to look for these emails, highlighting the row where more than one generic inbox is sent to, then sorting the document by highlights, which is taking hours. I want to automate this process.

What I'm looking for is some guidance on what functions would be best to do this.
I've tried things like Match, VLookup, Search, etc. But I'm not able to make this work for me.

Thanks in advance.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
Can you post some sample so we can find a solution?
I agree, but make sure the email addresses are not real as spammers routinely search forums like this for email addresses to spam. :cool:
 

Richard2806

New Member
Joined
Oct 28, 2019
Messages
5
Hi, thanks for the replies.
Sorry it's taken so long for me to get back to you.

It's a little tricky to post samples as some of the details are confidential, so I've created a dummy of it.

RecipientsSubject
Type

<tbody>
</tbody>
Receive type

<tbody>
</tbody>
State

<tbody>
</tbody>
Target1@somewhere.com.au,ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore1@dontcare.com.au
FW: Blah Blah Blah

<tbody>
</tbody>
received

<tbody>
</tbody>
Forward

<tbody>
</tbody>
Processed

<tbody>
</tbody>
ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore2@dontcare.com.au,ignore3@dontcare.com.auBlah Blah BlahreceivedNew
Processed

<tbody>
</tbody>
Target1@somewhere.com.au,Target3@somewhere.com.au;ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com.au,ignore231@dontcare.com.auBlah Blah BlahreceivedNew
Processed

<tbody>
</tbody>

<tbody>
</tbody>

The actual spreadsheet is HUGE. There a ton of lines like the above, and the first column is really the one I'm concerned about.

I have another spreadsheet that just as a list of email addresses to scan for:


Email addresses

<tbody>
</tbody>
Target1@somewhere.com.au
Target2@somewhere.com.au
Target3@somewhere.com.au
Target4@somewhere.com.au
Target5@somewhere.com.au

<tbody>
</tbody>

So the idea is, compare the recipients column with the list of email addresses and highlight any that appear in the Email addresses list.
The Recipients field can have 1 or a lot of email addresses in it. Sometimes it will have 1 of the target addresses, sometimes it will have 2 or more.
If there's more than 1 of those highlighted in the Recipients column, then that is a line I'm interested in. I'll filter out all the rest to make them go away, and only show the lines that have more than 1 of the targetted email addresses in them.

I hope that clarifies the issue?
(Not sure how I managed to get boxes around some of my table contents, just ignore that)
Thanks in advance for any advice you can offer.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
Is this what you want? If not, please also provide the expected results for the sample data, with any further explanation.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Just checking that your email addresses can be delimited by either a comma or a semicolon (you have used both in the sample) as the formula would be slightly simpler if only one delimiter was used.
Also, there is sometimes a space after the delimiter and sometimes not in the sample data. Is that true for your real data?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:482px;" /><col style="width:61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Recipients</td><td style="font-size:10pt; text-align:right; ">Count</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Target1@somewhere.com.au,ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore1@dontcare.com.au</td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore2@don...ontcare.com.au</td><td style="font-size:10pt; text-align:right; ">1</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Target1@somewhere.com.au,Target3@somewhere.com.au;ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com .au,ignore231@dontcare.com.au</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">Email addresses</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">Target1@somewhere.com.au</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">Target2@somewhere.com.au</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; ">Target3@somewhere.com.au</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; ">Target4@somewhere.com.au</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; ">Target5@somewhere.com.au</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=COUNT(SEARCH<span style=' color:008000; '>(";"&A$8:A$12&";",";"&SUBSTITUTE<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(A2,",",";")</span>," ","")</span>&";")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>
 
Last edited:

Richard2806

New Member
Joined
Oct 28, 2019
Messages
5
Hi Peter,

Yeah, sorry about that. The delimiter is always a comma, and there should not be any spaces.
This is a side project I'm trying to do at work, and I'm a bit swamped at the moment so I rushed through trying to get some dummy data to show you guys. Those errors or on me, not coming from the data.

I haven't had a chance to really test this, but I don't think this will do quite what I need.
Although the idea might work. Rather than highlighting the matched emails, I could use the column with the number of matches to do the filtering once I get it to work for my data that way.
If count is => 2, then highlight the line, or If count is <2 hide the line (something like that)

I was trying to steer away from having a column to do the processing for my data, and wanted to create a macro to do a kind of conditional formatting on Column A of the data, so that it would highlight the matched addresses, then hide any lines that do not have 2 or more matched email addresses per line. (Since those have only gone to one inbox, and as such would end up processed as they should be).
My aim is to find any that have gone to more than one of the matched inboxes, because they will create duplicate jobs, which I need to track down and resolve.

To give you more of an idea, the report that shows the emails coming in can be between hundreds and thousands of lines long, depending on the amount of incoming traffic for that period. I get two of those reports generated each day.

The list of inboxes they can be potentially sent to is around 600 (stored in a separate file, manually updated).

Ultimaltely I would like to create a macro that processes the report with the press of a single button, each time I open the report which hides all the lines I don't need to worry about, and highlights the inboxes that it did go to, that match the list, so that it's easier to see where the email ended up.

Sorry I can't just provide you with both the lists, because I know that would make this a lot easier.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
Sorry I can't just provide you with both the lists, because I know that would make this a lot easier.
The sample data is fine, especially now I know about the spaces, commas etc.
What I don't fully understand is exactly what you want. Is it this?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:482px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Recipients</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffcc00; font-size:10pt; ">Target1@somewhere.com.au,ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore1@dontcare.com.au</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore2@don...ontcare.com.au</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffcc00; font-size:10pt; ">Target1@somewhere.com.au,Target3@somewhere.com.au;ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com .au,ignore231@dontcare.com.au</td></tr></table>



Or this

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:482px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Recipients</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Target1@somewhere.com.au,ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore1@dontcare.com.au</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore2@don...ontcare.com.au</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">Target1@somewhere.com.au,Target3@somewhere.com.au;ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com .au,ignore231@dontcare.com.au</td></tr></table>

Or this

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:482px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Recipients</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffcc00; font-size:10pt; ">Target1@somewhere.com.au,ignore1@dontcare.com.au, Target2@somewhere.com.au,ignore1@dontcare.com.au</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffcc00; font-size:10pt; ">Target1@somewhere.com.au,Target3@somewhere.com.au;ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com .au,ignore231@dontcare.com.au</td></tr></table>


Or something else?
 

Richard2806

New Member
Joined
Oct 28, 2019
Messages
5
I guess option 2 or 3 would work. A combination of the two would be best.
I don't need to care about any of the lines that only have 1 of the target inboxes, only the ones with 2 or more.
So highlighting the matching email addresses and hiding the other lines would be perfect.

That way I know how many lines I have to deal with each report, and I can easily see which email it matched.
Most of the time this part won't be an issue because the number of recipients is mostly only a few, so the number of matches would be only 2 or 3, and easy to spot, but if we can get those emails highlighted as well, that would certainly be the best option.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,231
Office Version
365
Platform
Windows
I guess option 2 or 3 would work. A combination of the two would be best.
Option 2 requires a lot more work than option 3 so let's see if option 3 is sufficient.

For the layout below, I have made the email address range in D2:D6 a named range: email_addr
I then selected A2:A4 and applied the Conditional Formatting as shown.
Subsequently you could then put AutoFilter on column A and use the filter drop-down to 'Filter by Color' & select Green

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:482px;" /><col style="width:17px;" /><col style="width:17px;" /><col style="width:256px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; ">Recipients</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Email addresses</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; font-size:10pt; ">Target1@somewhere.com.au,ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore1@dontcare.com.au</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Target1@somewhere.com.au</td></tr><tr style="height:42px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore2@don...ontcare.com.au</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Target2@somewhere.com.au</td></tr><tr style="height:64px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#92d050; font-size:10pt; ">Target1@somewhere.com.au,Target3@somewhere.com.au,ignore1@dontcare.com.au,Target2@somewhere.com.au,ignore21@dontcare.com.au,ignore231@dontcare.com.au</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Target3@somewhere.com.au</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Target4@somewhere.com.au</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Target5@somewhere.com.au</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A2</td><td >1. / Formula is =COUNT(SEARCH(","&email_addr&",",","&A2&","))>1</td><td style="background-color:#92d050; ">Abc</td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#ff0000;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >A2</td><td >email_addr</td><td >=Sheet1!$D$2:$D$6</td></tr></table></td></tr></table></td></tr></table>
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top