match and align cells in two columns?

Hello,

I have to columns of words sorted alphabetically. There is an overlap of about 80%, i.e. 80% of the words are the same, but each column may have some words that don't exist in the other. I would like to shift the cells of both columns so each row has matching words and blanks are added where there is no matching word. I have created a two dummy tables below, "before" and "after" to demonstrate this and hopefully make it clearer. My question is, does anyone have any suggestions how I might do this efficiently other than manually inserting blanks and shifting cells.

Before
=====
Apple Bag
Bag Car
Car Fort
Dog Harry
Ford
Harry

After
======
Apple
Bag Bag
Car Car
Dog
Ford Ford
Harry Harry

Thanks,
Tim.

Originally Posted by Timski

Hello,

I have to columns of words sorted alphabetically. There is an overlap of about 80%, i.e. 80% of the words are the same, but each column may have some words that don't exist in the other. I would like to shift the cells of both columns so each row has matching words and blanks are added where there is no matching word. I have created a two dummy tables below, "before" and "after" to demonstrate this and hopefully make it clearer. My question is, does anyone have any suggestions how I might do this efficiently other than manually inserting blanks and shifting cells.

Before
=====
Apple Bag
Bag Car
Car Fort
Dog Harry
Ford
Harry

After
======
Apple
Bag Bag
Car Car
Dog
Ford Ford
Harry Harry

Thanks,
Tim.

This is one method. There are others.

I'm going to assume you can make a distinct list of all the values; if not, say so, and we'll explain how.

I'll assume you insert 3 rows in front of the columns listed above that have the values (ie. the above two columns are now in D and E) and I'll assume there are 10 values in column D and 15 in column E, for the sake of example.

In Column A, put your distinct list of all values.

In Column B, put the following formula in B1 and copy down:
=IF(ISERROR(MATCH(A1,$B$1:$B$10,0)),"",A1)

In Column C, put the following formula in C1 and copy down:
=IF(ISERROR(MATCH(A1,$C$1:$C$15,0)),"",A1)

Then, Copy these two columns and Paste Special->Values.

If it turns out all right, delete columns A, D, and E.

I attached an example that is prior to deleting the columns.

Scott

Thanks Scott, I haven't had a chance to put it into action yet, but I can see how it works and it should do the job perfectly. Cracking solution.

This worked for me. Thanks much!!!