Excel two columns with word list - find differences

Question

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!

Answer 1

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')

Answer 2

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.