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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Can you post some sample so we can find a solution?
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top