Creating Exception Reports

{ 23 Comments }

By Ashish Mathur · · , · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • 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

    Reply

    • 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.

      Reply

  • 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

    Reply

    • 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.

      Reply

  • 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.

    Reply

  • 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

    Reply

    • 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.

      Reply

      • 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

        Reply

  • 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

    Reply

    • 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.

      Reply

  • 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

    Reply