I have two lists that I need to match using email addresses (which to date has been the only reliable key, but please keep reading!) …
The first list is a list of Users (over 1000 rows) who need to complete some training – it has several columns but the two I’m focussed on are one that comprises the User’s “MainEmailAddress” and another that contains the User’s “AllEmailAddresses” in the format …
SMTP:emailname1@domain1+SMTP:emailname2@domain1+SMTP:emailname3@domain2+SMTP:emailname4@domain2 etc etc
… where the emailname can be in various formats eg firstname.surname, firstnamesurname, initialsurname, initial.surname and a few others thrown in for good measure and there are a number of domains – BUT … every User is different ie they may only have a single email address, or they may have n email addresses, which may span all emailname & domain options, or may not!
As a start I have used Text to Column, then removed the SMTP: preface to get “clean” email addresses in separate columns
The second list is a list of Users who have completed the training with the date completed – again it has several columns but the two I’m focussed on are “EmailAddress” and “DateCompleted” – and you’ve got it, I want to know which Users on the first list also appear on the second list (to get a % complete) – and which Users don’t appear (so we can chase them!) …
To date it’s been easy because the organisation I’m doing this voluntary work for have enforced the use of firstname.surname@theirprimarydomain for ALL systems, but following a recent merger with another charitable organisations, the second list might (and does!) use one of the other email addresses (not always the same format!) and I need a way to extend the XLOOKUP routine I’m using (as part of a macro that also opens the source lists, does some filtering/sorting, saves as an output file, etc, etc) to loop through every Users email addresses until a match is found, or return a null value …
But I can’t see the wood for the trees and I need some help please!
Any takers?
Many thanks
PS; I can’t share my files because they contain personal data – but I could create a short mock up if my explanation above doesn’t make sense …
The first list is a list of Users (over 1000 rows) who need to complete some training – it has several columns but the two I’m focussed on are one that comprises the User’s “MainEmailAddress” and another that contains the User’s “AllEmailAddresses” in the format …
SMTP:emailname1@domain1+SMTP:emailname2@domain1+SMTP:emailname3@domain2+SMTP:emailname4@domain2 etc etc
… where the emailname can be in various formats eg firstname.surname, firstnamesurname, initialsurname, initial.surname and a few others thrown in for good measure and there are a number of domains – BUT … every User is different ie they may only have a single email address, or they may have n email addresses, which may span all emailname & domain options, or may not!
As a start I have used Text to Column, then removed the SMTP: preface to get “clean” email addresses in separate columns
The second list is a list of Users who have completed the training with the date completed – again it has several columns but the two I’m focussed on are “EmailAddress” and “DateCompleted” – and you’ve got it, I want to know which Users on the first list also appear on the second list (to get a % complete) – and which Users don’t appear (so we can chase them!) …
To date it’s been easy because the organisation I’m doing this voluntary work for have enforced the use of firstname.surname@theirprimarydomain for ALL systems, but following a recent merger with another charitable organisations, the second list might (and does!) use one of the other email addresses (not always the same format!) and I need a way to extend the XLOOKUP routine I’m using (as part of a macro that also opens the source lists, does some filtering/sorting, saves as an output file, etc, etc) to loop through every Users email addresses until a match is found, or return a null value …
But I can’t see the wood for the trees and I need some help please!
Any takers?
Many thanks
PS; I can’t share my files because they contain personal data – but I could create a short mock up if my explanation above doesn’t make sense …
