Merging sheets

I have 2 marketing lists for my local area. They are in the same workbook as sheet 1 and sheet 2.

I need help to combine them into 1 woorksheet.

The problem I have is that the only common column is Column A (the Company Name) but not the same names but some are duplicates.

Sheet 1 has columns:
Company name ¦ Address 1 ¦ Address 2 ¦ Address 3 ¦ Town ¦ County ¦ Postcode ¦ Contact Title ¦ First name ¦ Surname ¦ Telephone number

Sheet 2 has columns:
Company name ¦ Address ¦ No. of Employees ¦ Postcode ¦ Telephone Number ¦ Type of Business ¦ Contact name

Is it possible to combine them and then look for duplicates and delete them?
I need to keep all information.

I have spent a few hours trying to do this manually and really didn't get anywhere.

You're help would be appreciated.

Hmmmm, heres' how I would do it.

Sounds like first you need to combine data if the company appears on both lists. then add any additional companies that are on one list but not the other.

First, Add Columns to your first list which are in the second list but not the first.

Name your two lists as follows;
Highlight the entire list 1 including headers and in the "name box" (that white box upper left where typically it shows a cell address like A1) type list1 then ENTER. Do the same with List 2.

In your list 1, in the new columns you created (e.g the # of Employees), assuming your in row 2, type

Please Login or Register  to view this content.

which says if list 2 doesn't contain the company (will return the #NA error), then put in nothing, otherwise return column 3 (# of employees). Drag that down. Do the same with the other columns changing the 3 value to represent the appropriate column you want out of list2.

When you're satisified with this, copy and paste special values for this whole list to replace the formulas with values.

Now, you want to get companies which are in list 2 but not in list 1.
In the first blank column of list 2, type in

Please Login or Register  to view this content.

This tells excel to put "No Match" in that cell if that company isn't in list1 and "Match" if it is. Drag that down to the end of the list.

Now do an autofilter on List 2 showing only "Match". Copy and paste those rows to the bottom of list 1 and move your columns appropriately.

Make sense?

ChemistB