Excel: How can I match two columns?

General Questions
#1

This one is giving me fits. I keep thinking I should be able to do it but I can’t firgure it out.

Title may be misleading…I actually have four columns (2x2)

Column A are email addresses such as:

123@abc.com
345@abc.com
678@abc.com
xyz@123.com
uvw@123.com
rst@123.com

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.

Thanks in advance!

#2

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.

#3

Can you give a small example of your required results.

#4

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.

#5

Never tried this before so will see if it works:



ColumnA          ColumnB          ColumnC          ColumnD
A@123.com     123.com           Z@123.com      123.com
B@123.com     123.com           Y@123.com      123.com
C@123.com     123.com
D@123.com     123.com

M@345.com     345.com           T@345.com      345.com
                                U@345.com      345.com
#6

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.

#7

Bummer. Was afraid of that.

Thanks.

#8

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?

#9

use this formula to extract the domain from the email address in col A1
put this in a temp work column maybe G1

=RIGHT(A1,LEN(A1)-SEARCH("@",A1,1))

let’s assume the temporary column is G1 and your domain column is B1

this will return the email address if true else it will be null
=IF(B1=G1,A1)

#10

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
#11

I may be missing what this does.

Seems you strip the domain and put it in column G.

Then you check column G to see if it matches the domain in B which of course it will. Then write what is in A1 in that cell.

Seems redundant.

#12

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.

#13

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.

#14

No, you’d simply use a pivot chart.

This:



List  E-Mail       Domain
A     abc@123.com  123.com
A     bcd@123.com  123.com
B     bcd@123.com  123.com
B     cde@456.com  456.com

Becomes:



Domain    E-mail        Total
123.com   abc@123.com   1
          bcd@123.com   2
123.com TOTAL           3

456.com   cde@456.com   1
456.com TOTAL           1

If you needed to get rid of one of those bcd@123.com addresses, you’d need to de-duplicate, of course.