compare two columns in excel 2007

February 26, 2009 at 11:31:20
Specs: Windows XP
The problem i am having is that i have two columns of names on the same sheet. i would like to remove the names that are in both columns and put in a 3rd column. for ex.

A1(mike d) B1(tommy y)
A2(jason h) B2(jason h)<--would be removed from both columns, and would be added to C1 and etc.

Formula or macro would be helpful, and where do i apply it?


See More: compare two columns in excel 2007

Report •

February 26, 2009 at 16:04:53
Google found this:

Looks like it's what you want.


Report •

February 27, 2009 at 05:17:18
Thanks mike, I tried that and nothing happened, that i think was for numbers. What i am looking for is like the post "compare two columns in excel" he was compairing names of companies, and i am just doing names of people. there was just no instruction on how to apply the code.

Report •

February 27, 2009 at 06:55:35
Try This:

1. Insert a column between Column A & Column B

2. Type ( or cut & paste) the following formula in cell B1:

=IF(ISERROR(MATCH(A1,$C$1:$C$150,0)),"","Match in Col C")

3. Select cells B1:B150
4. Then "Edit", select "Fill" on the Edit menu, and then click “Down”.

This will compare Column A against Column C the only difference is that instead of copying the contents of the matching cell I insert the comment "Match in Col C" next to the name in Column A matched in Column C.

The columns do not have to be sorted, although it might be easier.
I Started at Row 1, change the formula if needed.
I used only 150 rows to check, again change if needed.
I inserted a column, because I find it makes it visually easier to determine the outcome.


Report •

Related Solutions

February 27, 2009 at 07:45:43
In the thread in which you found the code, you said you tried to use it.

Where did you put the code before you tried it?

Is your data really in Columns A and B, stating in Row 1, with the result going into Column C? That's what the code was written for.

Assuming you are working with columns A & B as noted, right click the sheet tab for the sheet, choose View Code and paste the code into the code window that opens up.

Let us know what happens.

Report •

February 27, 2009 at 08:11:38
Another thing to remember is that a SPACE is considered a character.

If your comparing First & Last Names, if there is an extra space between the names in either column or there is an extra space at the beginning or end of the names, then the names will _not_ match.

It must be an exact match, spaces and all.


Report •

February 27, 2009 at 08:54:44
re: It must be an exact match, spaces and all.

If that's the problem, we might be able to use the Like operator, but I think we'd need to know a little more about the spreadsheet before we could suggest anything.

Report •

February 27, 2009 at 09:06:48
I was thinking TRIM would be easier??


Report •

February 27, 2009 at 13:32:49
True, TRIM (or CLEAN) might be use to help clean up the names before the code is run.

I was thinking more in terms of using the VBA Like operator within the code itself without cleaning the columns up first.

Like I said, though, we'd need to know more about the data before we could really suggest anything else.

Report •

March 3, 2009 at 12:49:24
Sorry for not replying sooner, i was sick. But that was the discrepency, there was a space, and i had to go line by line and remove it( i have tried several codes for that space like trim formulas,but no hope) Once i had that removed, code worked like a charm. And Mike thanks alot for the alternative code becuase i will have use for that as well. THank You all So much.

Report •

Ask Question