Column B are just the domains of the above addresses stripped out so would look like:
123.com 123.com 123.com
Columns C & D are done exactly the same way although it is a different list of names. The domains are frequently (not always) the same though (so, column C is unique but column D will have a great many in common with column B).
Basically these are lists for two separate e-mails we send out. My task is to bill the companies for the subscribers they have. Unfortunately, so far, the only way I can gather everyone from (say) 123.com is to manually cut and paste from each list. It is a long list though and insanely tedious.
My question is can I match the domain columns of the two lists and keep the names next to them in the right order? If I make them a table and sort on a column the columns, being unequal, do not match up. Note that there may be twenty “123.com” in one list and five in the other (or vice versa).
In short I want all the 123.com’s next to each other in both lists and then the XYZ.com’s and so on (with their corresponding email addresses next to them).
Not sure if this makes sense but if anyone has a bright idea for this would be much appreciated.
assume 8 rows in each (adjust the numbers as you need to)
column a: email1
column b: domain1
column c: email2
column d: domain2
column e: combined email (set e1=a1 etc, then e9=c1 expand to fill)
column f: combined domains (set f1=b1, f9=d1, expand to fill)
highlight e&f and copy, then using the same selection paste special: values and sort.
Are you using those second columns for anything more than tracking what the domain of the email address is? If so, you can replace that with something like "=if(isnumber(find(a1, “@abc.coM), 1, 2”. That would result in a 1 if the email had abc.com in it and a 2 if it didn’t. That might be more helpful in the middle of formulas. But I’m not quite clear on what your output is supposed to be like.
No. Excel can’t do this. It would require checking the subtotal counts of each email domain and insert extra rows to accommodate the greater of columns B or D for each. The Excel sort functionality doesn’t do this type of “merge sort.”
If you want to create a billing list, you’ll have to have a VBA code macro loop through both lists and create new worksheet that merges both lists in the sequence you want.
The quickest way right now would be to simply “Select All”, sort by Column X, copy and paste the list into a fresh document. Repeat with Columns Y & Z (and so on).
I’d guess you’re not selecting all the columns before you sort. Try selecting all columns with data (click and drag from the top of Column A (your cursor will turn into a down arrow), click and drag over to Column D (or ALL the way across the file until you have selected every single column with data). Now go to Data->Filter->Auto Filter.
Your example is missing a step. Is that what you’re starting from or what you want to end up with? Could you include both phases?
Is this the only data? Why are Columns C and D necessary? Why not have:
E-Mail Domain List 1 List 2
123@abc.com abc.com X
345@abc.com abc.com X X
678@abc.com abc.com X
xyz@123.com 123.com X
uvw@123.com 123.com X X
rst@123.com 123.com X
Generally the two lists are separate. They share some addresses but many are unique to one or the other. However, coming from the same company we bill for both on one invoice.
The mail lists are a download from ConstantContact which sends and maintains the list. So, I get an exported list of names a mile long for each. I then have Excel remove duplicates (since you only pay one sub whether you get one or both). I am left with the two remaining lists which have no common e-mail addresses but many domains in common (which I use to identify the company and send the bill).
To do it your way I would have to write VB code (which I am not good at) or manually tick off who is on what list. Not really an improvement.
I thought you wanted a list of email addresses that matched a specific domain in column b.
The first formula extracts the domain so it can be compared.
<shrug> I may have misunderstood what you wanted.
I often have to report on email address from a specific domain. It sounds like you need something different.