Merge sheets into one and remove duplicates with Copy and Paste
In Excel, there is no built-in function can quickly merge sheets and remove duplicates, you just can copy and paste the sheet contents one by one then apply Remove Duplicates function to remove the duplicates.
1. Select the contents in Sheet1 you use, press Ctrl+C to copy the contents, then go to a new sheet to place the cursor in one cell, press Ctrl + V to paste the first part.
2. Repeat above step to copy and paste all sheet contents into one sheet.
3. Select the combined contents, click Data > Remove Duplicates.
4. In the Remove Duplicates dialog, check or uncheck My data has headers as you need, keep all columns in your selection checked.
Tip: If your headers have been repeated in the selection, uncheck My data has headers, if not, check it.
5. Click OK. A dialog pops out to remind you the number of removed items and remain items.
6. Click OK. The sheets have been combined into one with all duplicates removed.
Merge sheets into one and remove duplicates with Kutools for Excel’s Combine function
If there are dozens or hundreds of sheets needed to be merged into one, manual copy and paste will be time-wasted. Fortunately, the Combine function of Kutools for Excel can quickly combine multiple sheets into one sheet.
1. Enable Excel, and click Kutools Plus > Combine, in the Combine Worksheets dialog, check Combine multiple worksheets from workbooks into one worksheet option.
2. Click Next. In the second Combine Worksheets window, click Add > File or Folder to open the Open dialog, then select the workbook you want to combine all sheets into one.
3. Then check the workbook in Workbook list pane you have added just now, go to the right section of Worksheet list pane, confirm the sheets you want to combine into one are checked.
4. Click Next, in the last step of the dialog, specify the setting of the combine sheet as you need. If you have one row header of each sheet, you just need to type 1 in the textbox of Title row number.
5. Click Finish. Select one location and give a name for the combined workbook.
7. Click Save. And after combining, a dialog pops out to remind you if open the combined workbook.
8. Click Yes to open the combined workbook, then the data have been combined into one sheet.
1. Select the main table you want to update with new data of other table, click Kutools Plus > Tables Merge.
2. In the popping Tables Merge dialog, select the second table in Select the lookup table section.
3. Click Next, in step 2, check the key column you use to compare two tables based on.
4. Click Next，in step 3，select the columns you will use to update the main table. In this case, I check all columns.
5. Click Next, in the step 5 (in this tutorial, there is no new column to be added, so the step 4 of the dialog has been skipped), check Add mismatched rows to the end of the main table option in Add options section, check Only update the cells when there is data in the lookup table option in Update options section.
6. Click Finish. Now the two tables are merged into the main table. You can see the main table has been updated with new data of the second table.