One worksheet shows the date wise courses (External Course, Internal Course and Elearning Course) taken by staff members. The data also shows the Line Managers that they report to. Another worksheet lists down all staff members and the Line Managers that they report to.

From data on the first worksheet, one can easily determine (via a Pivot Table) the Elearning courses taken by various staff members (along with their Line Managers). However, a Pivot Table will not show the staff members (along with their Line Managers) who did not take the Elearning Course.

You may refer to the data set and my workaround in this workbook. My solution uses the Microsoft Power Query add-in for MS Excel 2010 and higher versions. One can download and install the tool from here.

Hi Ashish

That is exactly what I was after - thank you very much!

I had not heard of Power Query but have now downloaded it and matched your results and have applied it to other similar analyses. This solves a long-running problem for me.

Thanks again

Jonathan

You are welcome.

Ashish,

I need to find and remove emails that "bounce" (maybe because the email no longer exists or other reasons) from my email list. Please help me somehow extract them from my master list.

Here is the link to the two files at the skydrive account you told me to sign up for:

https://skydrive.live.com/redir?resid=3DC5B7E6021BAB26!106

Thank you,

Steven

Hi,

Given the quantum of your data (All e-mail list being 38,747 rows long and bounced e-mail list being 740 rows long), using Advanced Filters or a formula such as VLOOKUP() or COUNTIF() in a spare column will wither crash MS Excel or cause processing to become terribly slow. Infact, I tried to extract the Valid and subscribed e-mail list to another worksheet using Advanced Filter but MS Excel crashed.

I therefore, used Power Query - a free add-in for MS Excel 2010 and higher versions. This tool can be downloaded from here.

You may refer to my solution in the "Valid and subscribed emails" worksheet of this workbook.

Hope this helps.

Ashish,

Thank you for that. Please email me the new file with the new email list.

Also, is there a solution or Excel 2007 - the only version I have?

Thanks again,

Steven

Hi,

The valid and subscribed email list is in the workbook (the link of which I have shared with you above). Anyways, here is the list.

I tried a version agnostic method (Advanced Filter) but because of size of the data, Excel crashed. You can try this

1. Write a VLOOKUP() function in the All email list worksheet. The table_array should be the range on the Bounced and unsubscribed list worksheet

2. Copy that formula all the way down and filter on #N/A's

3. Copy this list to a new worksheet. This should be your result

I hope the steps outlined above work for you.

I have a dump of 7000 sites where EB bills have been received from electricity boards for past 5 years. Period of each bill differs from 15 days to 3 months. There are certain bills which are missing for certain period. My query is how to develop a exception report for find out period for each site where bills have not been received. Have shared the file through google docs.

Hi,

You may refer to my macro based solution in this workbook.

Hope this helps.

Ashish,

The bounced list only (741 emails) is at that link. Please send the valid file.

Thank you,

Steven

Hi,

Please try now.

Yes, Thank you!

Is there a solution for Excel 2007? As a newer version is not financially possible at the moment, unless you know of an inexpensive way to purchase.

Steven

Hi,

Please refer to my reply on September 29, 2013.

Ashish,

I ran into another problem with the same list - about half of the emails came back as undeliverable. Is there a way to apply a formula or rule to get rid of excess formatting? Excess besides the email itself.

Thanks,

Steven

HI,

I do not understand your question at all. What can I do about "emails came back undeliverable"? We simply extracted all those e-mail addresses which were not available in the Bounced and unsubscribed e-mail list. If some of those e-mail addresses so extracted are incorrect, then they will obviously bounce.

What formatting are you talking about. Please explain your question clearly.

Ashish,

What we already did is fine, but I said I ran into "ANOTHER (not the same) problem." Those bounced and unsubscribed are taken out - yes and thank you. These other emails (I just discovered) are not formatted correctly for this program I am using. I think it may be because they have the names in before the email itself, but I'm not sure why it is rejecting them. The emails are good, so it must be something with the formatting. I'd like to extract the excess formatting of the email to see if that's the problem, so only the email address is left.

Thanks,

Steven

Hi,

Upload the file to SkyDrive and share the link of the uploaded file here. Please ensure that in the files which you upload, there are dummy e-mail addresses.

Ashish,

Here it is........

https://skydrive.live.com/redir?resid=3DC5B7E6021BAB26!106&authkey=!AGeKWmaqhj3Iir0

Thanks,

Steve

Hi,

Try this

1. Select A1:A19707 and press Ctrl+H

2. In the Find what box, type star and then less than symbol

3. Click on Replace All

4. In the Find what box, type greater then symbol

5. Click on Replace All

The process above will remove all names appearing before the e-mail.

Hope this helps.

Ashish,

after the first command (*) it replaced all, but they are still not formatted wit just the emails. Here's an example, there are many like this one: dr.jasongastrich<jason@jcsm.org

Thanks,

Steve

Ashish,

The following list has some emails with names in the row with the emails. I need the names extracted, and only the emails left please.

Here is the list: http://sdrv.ms/17PTCjp

Thank you,

Steven

Hi,

Just follow the first 3 steps of my solution which I shared on October 5, 2013. I tried the steps and I received a message saying that 37 replacements were made.

Ashish,

It seemed to work. I will know for sure when I try to upload them to a program in a few days.

Thank you!

Steven

You are welcome.