+ Reply to Thread
Results 1 to 2 of 2

Need to know who was removed or added from a list of email addresses

  1. #1
    Registered User
    Join Date
    11-19-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    17

    Need to know who was removed or added from a list of email addresses

    Ok, so at work right now, I'm exporting around 30,000 user's mailboxes due to a legal hold. The Security department is providing me a list of users in an Excel sheet. (Really there were 100,000 users but I've already used a vlookup to figure out which users we didn't have archives for.) But, yesterday they sent a new revised list removing about 10,000 users from the original list and adding about 18 users. But tomorrow they could send me a list asking me to add 5,000 users and remove 200. So, I'm trying to figure out a way to keep track of this. I'm attaching a sample spreadsheet. I have a main tab that has email addresses. Down to the line where I wrote "Added from 2-14 Update", those users were the original users provided by security. Then they sent me an updated list that listed everyone they wanted exported. Now I can't just use the new list to export the users. The first list they provided me had the user's display name, their account name, and email address. I can't export based on email address. That's just the way it works. The updated list only had the user's email addresses. Nothing else. I had to then fiilter the original main tab from the list they provided me using a vlookup to tell if a user on the new list wasn't on the old list. That worked, except there were the 18 users they added. 18 isn't a big deal to add, but if they would've sent me 1,000 users added to their updated spreadsheet, I need the function to see that it's not on the main spreadsheet and add a line to the main spreadsheet with the user it found on the updated spreadsheet to the main spreadsheet. But Security could send me another list tomorrow that will be revised again. I need for the function to somehow say well, the email address was on the main tab, it wasn't on the 2-1 tab, but it is on the 2-14 tab. So, because 2-14 is the later date, add it to the main sheet. But if it's not on either of the 2-1 or 2-14 tab, remove it. Then if they add a brand new email address on 2-14, add it to the main tab.

    I dont know if this is possible.

    Attached is an example spreadsheet simliar to what i'm using at work. (the one at work has a lot more info than just the email address.) The main sheet, if it sees an email is removed, needs to remove the whole line for the user with the display name and everything else.
    Attached Files Attached Files
    Last edited by dorlow; 02-14-2020 at 08:56 PM.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Need to know who was removed or added from a list of email addresses

    If you have 2 lists, presumably single columns, Old!B2:B30011 and New!B2:B22029 (made up numbers of rows), you can see which are in both like so.

    Old!C2: =MATCH(B2,New!$B$2:$B$22029,0)

    Fill Old!C2 down into C3:C30011. These formulas will produce numbers when the values in Old col B have matching values in New col B, and #N/A return values will indicate values in Old col B which have been removed in New col B.

    New!C2: =MATCH(B2,Old!$B$2:$B$30011,0)

    Fill New!C2 down into C3:C22029. These formulas will produce numbers when the values in New col B have matching values in Old col B, and #N/A return values will indicate values in New col B which have been added.

    Just to make sure of things, check the value of =COUNT(Old!C2:C30011)=COUNT(New!C2:C22029) . If that returns TRUE, then all the rows in either Old or New with numbers in col C are common to both lists. Otherwise, there could be duplicates in one or the other.

    Best to create a 3rd list (to become Old when you receive the next list) with 2 columns. Copy the records in common from either Old or New col B into the 3rd list's 1st column. Then enter common into the 2nd columns for all of these. Copy the Old col B values with #N/A in Old col C into the 3rd list below the common values, and enter removed in the 3rd list's 2nd column. Copy the New col B values with #N/A in New col C into the 3rd list below the removed values, and enter added in the 3rd list's 2nd column.

    You could use Autofilters to make it easier to filter Old and New cols B and C together, excluding #N/A on the 1st passes, including only #N/A on the 2nd passes for creating the 3rd list.

    Without macros, this is going to be a manual process, but with Autofilters, it shouldn't be that difficult.

    Another, more database-like approach: generate a master table with mailbox IDs as the 1st field, date for the list in which that ID first appeared as the 2nd field, date for the list in which that ID disappeared or blank if it's still in the latest list as the 3rd field. There could be multiple records for a given ID, but they should all have different 2nd field (appearance date), and at most one record should have a blank 3rd field (removal date). This may be the most reliable approach if you'd need to provide records retention reports to a court.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sending Email to list of addresses from worksheet
    By seekon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2020, 03:33 AM
  2. Replies: 3
    Last Post: 11-29-2014, 07:30 AM
  3. Removing email addresses from list
    By excelquestion1 in forum Excel General
    Replies: 12
    Last Post: 08-16-2013, 05:47 AM
  4. [SOLVED] List of Email Addresses that I need to Concatenate into one cell
    By amyp22x3 in forum Excel General
    Replies: 3
    Last Post: 01-17-2013, 03:42 PM
  5. How can you select email addresses from a list and then have a sheet sent
    By kevin316 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2009, 01:46 PM
  6. Look up a list of email addresses?
    By akopp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2008, 05:54 PM
  7. Excel List of Email Addresses
    By Jenny in forum Excel General
    Replies: 3
    Last Post: 05-23-2006, 05:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1