Merge 3 worksheets into 1 workbook and delete duplicates

[LAConsulting1]

I have 3 worksheets with similar information. Each worksheet has the same column titles, i.e. First Name, Middle Name, Last name, etc. I can copy and paste the information of Worksheet 1 into Worksheet 2 underneath the existing information, however, there may be duplicates. How do I delete duplicates in columns without it affecting the rows? For example, if A2 (Mary) and A7 (Mary) are the same first name, but C2 (Thompson) and C7 (James) are different last names, I do not want to delete box A7 (Mary) because then A8 (Jenny) will have moved up and will not correspond with C8 (Smith) anymore. It will look like I don't have Mary James, but now will have a Jenny James. I want the rows to be locked, but I want to delete duplicated data. For example, if after I copied and pasted Workbook 1 into Workbook 2 and I have two entries for John Doe, I want to eliminate one John Doe.

Sorry if that was confusing, I wasn't sure how to explain it. I have attached an example work book where I want to combine worksheet 1 and 2 into worksheet 3. I want to keep the data on worksheet 1 and 2 separate and untouched from worksheet 3. I want worksheet 3 to be a combination of the first two without duplicate data. Please help!
Test Contact Merge.xlsx

[TMS]

Copy all the data from the three sheets into the new sheet.

Then use Remove Duplicates on the Data tab. Unselect all columns then select First, Middle and Last Names

Regards, TMS

[LAConsulting1]

Okay so, I have done that with my test work book. After selecting the three columns, it removed the four duplicates I had. Now, I knew there were exactly 4 duplicates in the test because I created it. I don't know exactly how many duplicates there are in the real three worksheets I will be working with. Also, in the test workbook, when I completed the instructions you gave me - it only moved those three columns up. The other information pertinent to each person did not stay with those people. If you'll see in the v2 I have uploaded, there are now blank cells and floating data. I need to make sure the rows stay locked, or rather Mary stays with Ivers and the phone number, city, and email, etc. all stay with that same name. Does that make sense?Test Contact Merge v2.xlsx

[TMS]

I'm guessing you had the three name columns selected on the worksheet. That wasn't what I meant. Don't do that. Unselect all the columns in the dialogue box and select the three name columns in the dialogue box

[TMS]

You could use COUNTIFS to highlight duplicates:

Please Login or Register  to view this content.

copied down

[xladept]

I followed TMS directions with the addition of unchecking the headers box and it worked very nicely