# Excel: How can I match two columns?
**URL:** https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181
**Category:** Factual Questions
**Created:** [January 8, 2010, 5:52pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181 "2010-01-08T17:52:35Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)
#### Post date: [January 8, 2010, 5:52pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/1 "2010-01-08T17:52:35Z")
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](mailto:123@abc.com)
[345@abc.com](mailto:345@abc.com)
[678@abc.com](mailto:678@abc.com)
[xyz@123.com](mailto:xyz@123.com)
[uvw@123.com](mailto:uvw@123.com)
[rst@123.com](mailto:rst@123.com)
Column B are just the domains of the above addresses stripped out so would look like:
> **[ABC Network - ABC.com](https://abc.com/)**
>
> Watch the ABC Shows online at abc.com. Get exclusive videos and free episodes.
> **[ABC Network - ABC.com](https://abc.com/)**
>
> Watch the ABC Shows online at abc.com. Get exclusive videos and free episodes.
> **[ABC Network - ABC.com](https://abc.com/)**
>
> Watch the ABC Shows online at abc.com. Get exclusive videos and free episodes.
[123.com](http://123.com)
[123.com](http://123.com)
[123.com](http://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](http://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](http://123.com)” in one list and five in the other (or vice versa).
In short I want all the [123.com](http://123.com)’s next to each other in both lists and then the [XYZ.com](http://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!
---
### Author:  [@pan1](https://boards.straightdope.com/u/pan1)
#### Post date: [January 8, 2010, 6:05pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/2 "2010-01-08T18:05:15Z")
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.
---
### Author:  [@ScarletSteve](https://boards.straightdope.com/u/ScarletSteve)
#### Post date: [January 8, 2010, 6:06pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/3 "2010-01-08T18:06:18Z")
Can you give a small example of your required results.
---
### Author:  [@Chessic\_Sense](https://boards.straightdope.com/u/Chessic_Sense)
#### Post date: [January 8, 2010, 6:25pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/4 "2010-01-08T18:25:02Z")
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](http://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.
---
### Author:  [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)
#### Post date: [January 8, 2010, 6:26pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/5 "2010-01-08T18:26:06Z")
> [@ScarletSteve](#):
>
> Can you give a small example of your required results.
Never tried this before so will see if it works:
```auto
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
```
---
### Author:  [@Ruminator](https://boards.straightdope.com/u/Ruminator)
#### Post date: [January 8, 2010, 6:28pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/6 "2010-01-08T18:28:08Z")
> [@Whack-a-Mole](#):
>
> My question is can I match the domain columns of the two lists and keep the names next to them in the right order?
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.
---
### Author:  [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)
#### Post date: [January 8, 2010, 6:31pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/7 "2010-01-08T18:31:44Z")
> [@Ruminator](#):
>
> 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.
Bummer. Was afraid of that.
Thanks.
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 8, 2010, 6:35pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/8 "2010-01-08T18:35:40Z")
> [@Whack-a-Mole](#):
>
> 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](http://123.com) is to manually cut and paste from each list. It is a long list though and insanely tedious.
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).
> [@](#):
>
> If I make them a table and sort on a column the columns, being unequal, do not match up.
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?
---
### Author:  [@aceplace57](https://boards.straightdope.com/u/aceplace57)
#### Post date: [January 8, 2010, 6:55pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/9 "2010-01-08T18:55:11Z")
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)
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 8, 2010, 7:05pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/10 "2010-01-08T19:05:34Z")
> [@Whack-a-Mole](#):
>
> ```auto
>
> 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
>
> ```
Is this the only data? Why are Columns C and D necessary? Why not have:
```auto
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
```
---
### Author:  [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)
#### Post date: [January 8, 2010, 7:08pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/11 "2010-01-08T19:08:44Z")
> [@aceplace57](#):
>
> 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)
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.
---
### Author:  [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)
#### Post date: [January 8, 2010, 7:12pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/12 "2010-01-08T19:12:36Z")
> [@Munch](#):
>
> Is this the only data? Why are Columns C and D necessary? Why not have:
>
> ```auto
>
> 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.
---
### Author:  [@aceplace57](https://boards.straightdope.com/u/aceplace57)
#### Post date: [January 8, 2010, 7:23pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/13 "2010-01-08T19:23:34Z")
> [@Whack-a-Mole](#):
>
> 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.
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.
\ 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.
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [January 8, 2010, 7:26pm UTC](https://boards.straightdope.com/t/excel-how-can-i-match-two-columns/524181/14 "2010-01-08T19:26:28Z")
> [@Whack-a-Mole](#):
>
> 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.
No, you’d simply use a pivot chart.
This:
```auto
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:
```auto
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](mailto:bcd@123.com) addresses, you’d need to de-duplicate, of course.