# Excel two columns with word list - find differences

Hi I'm not sure whether this is even possible, I have excel with two columns filled with words. In each column there is a word that is not in the other column and there are words which are in both. To illustrate, what I have:

``````aaa   bbb
bbb   ccc
ddd   ddd
eee   fff
``````

and I want result like this:

``````aaa
bbb   bbb
ccc
ddd   ddd
eee
fff
``````

Is this possible?

Thank you!

• using a macro, yes. using built in functionality, i am not sure. – Xantec Mar 25 '11 at 15:03
• well if I can't do it with buil in func I can do it in .net. I was hoping to save time doing it in excel – Robert Mar 25 '11 at 15:11

## 2 Answers

You are probably going to need to write a macro for that.

In pseudo code I'm thinking about:

``````FOR EACH CELL 'C' IN COLUMN 'A'

FIND CELL 'D' in COLUMN 'B' SO THAT VALUE('C') EQUALS VALUE('D')

MOVE TOP ROW() OF {'C','D'} AND FOLLOWING DOWN SO THAT ROW('C') EQUALS ROW('D')
``````

Here's one approach:

In a new sheet, copy column B underneath column A so that it is one long list. Then Remove all duplicates:

1. Highlight the whole list
2. Press ALT+D, F, A
3. Select 'Copy to another location'
4. Check 'Unique Records Only'
5. Select a cell to copy to in the 'Copy to' field.
6. Click Ok

In the cell to the right of your new unique list use the following formula and copy all the way to the end of the list:

``````=IF(ISNA(VLOOKUP(D2,\$A\$2:\$A\$10,1,0)),"",VLOOKUP(D2,\$A\$2:\$A\$10,1,0))
``````

Where:

1. D2 = The first cell of your unique list.
2. \$A\$2:\$A\$1 = The first column of your original lists.

Next to the cell where you entered the above formula enter this:

``````=IF(ISNA(VLOOKUP(D2,\$B\$2:\$B\$10,1,0)),"",VLOOKUP(D2,\$B\$2:\$B\$10,1,0))
``````

Where:

1. D2 = The first cell of your unique list.
2. \$B\$2:\$B\$1 = The second column of your original lists.

This should give you what you're looking for.

Note: I don't know what version of Excel you have. There are easier ways to do some of these steps in later versions.